user240179 user240179 - 7 months ago 10
SQL Question

SQL Joining single table where value does not exist

I've tried a lot of techniques though I can't get the right answer.
For instance I have table with Country names and IDs. And I want to return only distinct countries that haven't used ID 3. Because if they have been mentioned on ID 2 or 1 or etc they still get displayed which I don't want.

SELECT DISTINCT test.country, test.id
FROM test
WHERE test.id LIKE 2
AND test.id NOT IN (SELECT DISTINCT test.id FROM test WHERE test.id LIKE 3);

Answer
SELECT DISTINCT c1.name 
FROM countries c1
WHERE NOT EXISTS (
  SELECT 1 
  FROM countries c2 
  WHERE c1.name = c2.name
  AND c2.id = 3
)