CODEWITHSUNDEEP
sqlhaving
jan
jan

Reputation: 53

sql query select records that not having

I'm having difficulties to write SQL query that returns me IDs for which there is no typeA records for example

ID | type
1  | typeA
1  | typeB
1  | typeC
2  | typeB
2  | typeC
3  | typeB

this query should return IDs 2 and 3

thanks in advance for any suggestions
Jan

Upvotes: 1

Views: 202

Answers (2)

Robert Kock
Robert Kock

Reputation: 6018

Try this:

SELECT t1.id
FROM   t t1
WHERE  t1.id NOT IN (SELECT DISTINCT t2.id FROM t t2 WHERE t2.type = 'typeA');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use:

select id
from t
group by id
having sum(case when type = 'TypeA' then 1 else 0 end) = 0;

In many databases, you could also use except/minus:

select id
from t
except  -- or minus
select id
from t
where type = 'TypeA';

Upvotes: 2

Related Questions