Normalized data querying

master

id
1
2
3
..


detail

id data
1 A
1 B
2 A
...


1. get all that has A, easy.
2 get all that has A or B, easy.
3. get all that has A and B ?
I can think of group_concat & some string functions but that is not standard SQL.

couldn't you do

select * from master INNER JOIN detail ON master.id=detail.id

id data
1 A
1 B
2 A
2 B
2 C
3 A
3 C
4 ...
...


3 should not be found.

Something like should work, I think...

SELECT a.id FROM table a, table b WHERE a.data = 'A' AND b.data = 'B' AND a.id = b.id

If not, if you subselect the table where all the data is 'A', do a second subselect where the data is 'B' then join it will def work.

Keleth is right: Join the table to itself. Only good way.