CODEWITHSUNDEEP
mysqldatabaseselect
Gemmi
Gemmi

Reputation: 1242

MySQL select one to many

table1:

PRP_ID  |     NAME      
----------------------
100     |   something_100        
101     |   something_101         
102     |   something_102

table2:

ADN_ID |     NAME      
----------------------
11     |   thing_11        
22     |   thing_22         
33     |   thing_33

table3:

ID |     ADN_ID   |  PRP_ID   | VALUE
----------------------------------------
1  |       11     |    100    | YES
2  |       22     |    100    | YES
3  |       11     |    101    | NO
4  |       22     |    101    | YES
5  |       33     |    102    | YES

This is my database structure. I would like to select PRP_ID from last table where ADN_ID IN(11,22) and WHERE VALUE = YES for both ADN_ID

It should by very simple but I don't have idea how to do it.

SELECT PRP_ID
FROM  table3
WHERE ADN_ID IN(11,22)
AND VALUE = YES
GROUP BY PRP_ID

This won't work because it checks only ADN_ID 11 value

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You can add a having clause:

SELECT PRP_ID
FROM  table3
WHERE ADN_ID IN (11,22) AND VALUE = 'YES'
GROUP BY PRP_ID
HAVING COUNT(DISTINCT ADN_ID) = 2;

Upvotes: 2

Related Questions