List:General Discussion« Previous MessageNext Message »
From:Jason Trebilcock Date:November 22 2012 4:34pm
Subject:RE: Basic SELECT help
View as plain text  
Having watched responses go back and forth, I'll throw my cave-man approach
into the mix.

select id from
(select distinct id, count(*) 
from my_table 
where type in (2,5)
group by id
having count(*) = 2)a;

And addressing one of your concerns about more than two variables...in this
example,you would have to update the values in the where clause and the
count.

It ain't the prettiest...and not ideal from a performance perspective, but
it does work. I guess it kind of depends on how far the real-world problem
strays from this small example.

> -----Original Message-----
> From: Neil Tompkins [mailto:neil.tompkins@stripped]
> Sent: Thursday, November 22, 2012 8:30 AM
> To: [MySQL]
> Subject: Basic SELECT help
> 
> 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

Thread
Basic SELECT helpNeil Tompkins22 Nov
  • Re: Basic SELECT helpMike OK22 Nov
  • Re: Basic SELECT helpmarek gutowski22 Nov
    • Re: Basic SELECT helpNeil Tompkins22 Nov
      • Re: Basic SELECT helpBenaya Paul22 Nov
        • Re: Basic SELECT helpNeil Tompkins22 Nov
  • RE: Basic SELECT helpJason Trebilcock22 Nov
    • Re: Basic SELECT helpMichael Dykman22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
        • Re: Basic SELECT helpMichael Dykman22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
  • Re: Basic SELECT helphsv22 Nov
Re: Basic SELECT helpMogens Melander22 Nov
Re: Basic SELECT helpMogens Melander22 Nov
  • Re: Basic SELECT helpMichael Dykman22 Nov
Re: Basic SELECT helphsv23 Nov
  • Re: Basic SELECT helpShawn Green19 Dec