From: hsv
Date: November 23 2012 12:14am
Subject: Re: Basic SELECT help
List-Archive: http://lists.mysql.com/mysql/228706
Message-Id: <B0120627759@mailhome.tbbs.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

>>>> 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.