Claudio This is the solution i decided to go for as provided in a previous response.
Thanks Neil On 23 Nov 2012, at 00:41, Claudio Nanni <claudio.na...@gmail.com> wrote: > On 11/22/2012 04:10 PM, Ben Mildren wrote: >> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; > Ben you were almost there ;) > > SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=<num > of params> > > The only bad is the hardcoded parameter in the HAVING, may be it might be > improved. > > Anyway if the query is handwritten then you just hand-modify that too, > if it is built from code I can't imagine counting the parameters in the code > being so hard. > > Cheers > > Claudio > > >> >> 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 >>>> >>>> > > > -- > Claudio > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
