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
