Noob Noob - 1 year ago 67
SQL Question

find rows where a combination does not exist SQL

I need to find all rows in a SQL table where a certain combination does not exist. For example, consider the below table.

ID Column_1 Column_2
1. ID1 ABC
2. ID1 XYZ
3. ID1 QWE
4. ID2 XYZ
5. ID2 QWE
6. ID3 XYZ
7. ID3 ABC
8. ID3 QWE

In the above table, I need to return ID2 as there's no (ID2,ABC) combination. I am currently doing a WHILE and IF EXISTS for each ID in the table, but, is there a more efficient way? The volume is large here.

UPDATE: All of the below answers seem to return the expected values. Does one method have an advantage over others?

Answer Source

could be you need a not in and subselect

 select distinct column_1 
 from my_table 
 where column_1 not in (select column_1 from my_table 
                        where column_2 ='ABC');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download