SQL command to search records and remove entire group if x is located

Question

SQL DB2

This is what I have to start with

SELECT DISTINCT     
Last_Name, First_Name, State

FROM    AddressTable;

I am trying to modify the query to look through AddressTable and if State = IL then everyone with the same Last_Name should be filtered out.

If my results were below then I would want all of the records with last name Jones removed

Smith, Bob, MO
Smith, Eric, MO
Jones, Bob, MO
Jones, Eric, IL
Jones, Tim, NE

Alisa · Answer 1 · 2014-06-20T03:32:28.673

1

select * 
    from AddressTable 
    where Last_Name not in 
        (
        select Last_Name 
        from AddressTable
        where State = 'IL'
        )

edited Jun 20 '14 at 03:32

answered Jun 19 '14 at 18:45

Alisa

2,892
3
31
44

1

For this kind of `DELETE` statement, you won't need `DISTINCT` (and that clause as part of an `IN` clause is meaningless too). However, I'm pretty sure the OP is simply looking to _not include_ records in the `SELECT` result set, not actually remove them from the source table. – Clockwork-Muse Jun 20 '14 at 02:50

score 0 · Answer 2 · answered Jun 19 '14 at 18:34

I'm not clear on whether you want to delete rows, or if you just want to filter rows. Here rows are filtered:

SELECT DISTINCT Last_Name, First_Name, State
FROM    AddressTable X
WHERE NOT EXISTS (
    SELECT 1 FROM AddressTable Y
    WHERE X.NAME = Y.NAME 
      AND Y.STATE = 'IL'
);

SQL command to search records and remove entire group if x is located

2 Answers2