1

Suppose I have a table like: 

id............value
```````````````````
A............1
A............2
A............3
B............1

and so on.

so, If I want to select rows with Value either 1 OR 2 OR 3, I do this:

select id From table where value in (1,2,3)

But, what I want to do is, select id with AND instead of OR. I want it like

so If I want to select rows where Id has value 1, 2 AND 3, (in this case, resultant = A), how do I select that?

Thanks!
(I tried to do it myself, no luck, I tried to search on google, I realized I dint even know how to phrase the question! so if the question here doesnt agree with my actual doubt, feel free to change it)

iamserious
  • 5,385
  • 12
  • 41
  • 60
  • Nearly identical to http://stackoverflow.com/questions/2860374/is-there-something-in-mysql-like-in-but-which-uses-and-instead-of-or – Mark Byers Aug 23 '10 at 15:34

1 Answers1

6
select id 
from MyTable
where value in (1, 2, 3) 
group by id
having count(distinct value) = 3
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • the value will be passed from codebehind. its not a constant, but I Get the idea... i guess i will split the CSV values and count(*) it and place that variable instead of 3, would that work? – iamserious Aug 23 '10 at 15:07
  • @iamserious: that will work, as long as the number for 3 matches the number of distinct values - make sure your value passed from codebehind has no duplicates in it. – D'Arcy Rittich Aug 23 '10 at 15:19
  • I simplified the case here, but my querry was way more complicated.. and finally after trial-error method, i got it to work, but, all thanks to you, your querry works flawlessly, without any change! cheers! thanks a ton! you just saved me a lot of while loop debugging and coding! – iamserious Aug 24 '10 at 08:43
  • @iamserious: np - keep naming your tables MyTable and most queries on SO will work ;) – D'Arcy Rittich Aug 24 '10 at 12:35
  • hahaha!! thanks for the tip, btw my table name is really not "table" ! I just renamed it here!! but i like your sense of humour! (now that my problem is solved!!) – iamserious Aug 25 '10 at 16:29