>>>> 2012/11/22 14:30 +0000, Neil Tompkins >>>>
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
<<<<<<<<
This ugly one, which generalizes:
select id,group_concat(type) AS tl from the_table group by id having find_in_set('2',tl)
and find_in_set('5',tl)
Ugly becaus it involves so much converting between number & string.
For full generality one would indeed write
GROUP_CONCAT(type ORDER BY type)
and pass my "tl" and a string, say '1,2', to a procedure that using SUBSTRING_INDEX taking
the strings for arrays ensures that all found in the first string is also in the second
string. There are times when I wish SQL had arrays.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.