*HAVING typelist = 'x,y,z'; On 22 November 2012 15:25, Ben Mildren <ben.mild...@gmail.com> wrote: > Ah read it quickly and misread your requirement. Joins are likely FTW > here. The alternative would be to do something like this, but I'd opt > for the joins if you have a reasonably sized data set: > > SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable > WHERE id IN(x,y,z) GROUP BY id HAVING listid = 'x,y,z'; > > On 22 November 2012 15:10, Ben Mildren <ben.mild...@gmail.com> wrote: >> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; >> >> On 22 November 2012 15:01, Neil Tompkins <neil.tompk...@googlemail.com> >> wrote: >>> Michael, >>> >>> Thanks this kind of works if I'm checking two types. But what about if I >>> have 5 types ? >>> >>> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman <mdyk...@gmail.com> wrote: >>> >>>> response did not go to the list.. >>>> >>>> >>>> I assume that you mean the id must be associated with both type=5 AND >>>> type=2 as opposed to type=5 OR type=2; >>>> >>>> in some dialect of SQL (not mysql) you can do this: >>>> select distinct id from 'table' where type=5 >>>> intersect >>>> select distinct id from 'table' where type=2 >>>> >>>> >>>> As INTERSECT is not avilable under mysql, we will have to go the JOIN route >>>> >>>> select distinct a.id from mytable a >>>> inner join mytable b on (a.id=b.id) >>>> where a.type= 2 and b.type = 5; >>>> >>>> - michael dykman >>>> >>>> On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins >>>> <neil.tompk...@googlemail.com> wrote: >>>> > Hi, >>>> > >>>> > 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 ? >>>> > >>>> > Neil >>>> >>>> >>>> >>>> -- >>>> - michael dykman >>>> - mdyk...@gmail.com >>>> >>>> May the Source be with you. >>>> >>>> >>>> -- >>>> - michael dykman >>>> - mdyk...@gmail.com >>>> >>>> May the Source be with you. >>>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: http://lists.mysql.com/mysql >>>> >>>>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
