CODEWITHSUNDEEP
sql
user240179
user240179

Reputation: 39

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);

Upvotes: 1

Views: 64

Answers (4)

Ariful Haque
Ariful Haque

Reputation: 888

I think your situation is like,

Table Data

you need to result like , Expected Result Data

Then the Script will be like ,

SELECT DISTINCT t1.country 
FROM test t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM test t2 
  WHERE t1.country = t2.country
  AND t2.id = 3
)

Upvotes: 1

xQbert
xQbert

Reputation: 35323

you want to eliminate the countries having an ID of 3, not the ID's themselves...

SELECT Distinct t.country, T.ID
FROM test t
where not exists 
   (Select 1 from test t2 where t2.country = t.country and ID = 3)

Or

SELECT DISTINCT test.country, test.id
FROM test
WHERE test.country NOT IN (SELECT DISTINCT test.country FROM test WHERE test.id =3);

Upvotes: 1

Paul L
Paul L

Reputation: 938

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

Upvotes: 0

FelipeDrumond
FelipeDrumond

Reputation: 64

I'm now sure I understood your question, but if you want distinct countries where id is not 3, you just need this:

select distinct c.name from Countries
where c.id <> 3

Upvotes: 1

Related Questions