Dheeraj Nayak Dheeraj Nayak - 6 months ago 9
SQL Question

Retrieve only rows whose type is 're' and not 'res'

I want to get only those rows whose type is only 're' and not 'res' i.e output in the below example will be 2 and 4.

Table structure and data

Id type
1 res
1 req
2 req
3 res
3 req
3 res
4 req


I have tried this:

SELECT A.id
FROM demo AS A
INNER JOIN demo AS B
ON B.id = A.id
WHERE type = "req"


But I'm not getting the proper result.

Answer

That gets you all ids having only req entries

Select id 
from demo
group by id
having sum(type <> 'req') = 0