Take the 2-minute tour ×

I have 2 tables. Table A has just one column called ID which has a list of Ids 1 ,2 ....n Table B has 3 columns: ID (references ID column in table A); key,value So table B goes like this:

1   x   true
1   y   false
1   z   true
2   x   false
2   y   false
2   z   false

.. ..... So each ID from table A has got 3 entries in table B I need a query that fetches all Ids from table A that has got x,y, and z all marked as false in table B. So if any of x, y, z is true for a particular Id, we do not select it. I tried this but this is wrong:

select A.id from A,B where A.id = B.id and B.key in ('x','y','z') and B.value = 'false'

Can you please help me with the right query?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Nearly there:

select A.id 
from A,B 
group by A.id
having count(distinct B.key)=3
share|improve this answer
Thank you very much sir. This is super elegant –  Victor Dec 29 '11 at 17:29
add comment
select id from a where a.id not in (select id from b where value = 'true') 

should do the trick

It does assume that there is a x, y and z entry for every id.

share|improve this answer
I think the question implies that B.key can have values other than 'x', 'y' or 'z'. –  Mark Bannister Dec 29 '11 at 17:20
Possible. I let him decide by picking your answer or mine or provide more details. :-) –  Jens Schauder Dec 29 '11 at 17:22
add comment

Your Answer


