0

Possible Duplicate:
MySQL select join where AND where

Well, I have a table - 

 id  map_id     
 -----------
 100   1
 100   2
 101   1
 101   2
 101   3
 102   3

Now, I need to do an AND and OR operation on this data. That is,

OR - I have to get the id of all the rows with map_id 1 or 3. So, if I look for 1 or 3 the query should return 

   100
   102

I can do this simply by

Select id from table where map_id IN (1,3)

AND - If I query for 1 and 2, then I want the ids 100 and 101. And, this I am not able to find a query for.

I initially thought a simple query like this should work but I was wrong.

Select id from table where map_id = 1 AND map_id = 2 [Doesn't work]

I want to get a result like this but my query doesn't give me this. I don't want to resort to sub queries or joins, well, unless I really don't have to. I tried grouping by ids and using different having clauses but I found a solution yet.

   100
   101
Community
  • 1
  • 1
pavanred
  • 12,717
  • 14
  • 53
  • 59

1 Answers1

3

Try this

select id 
from yourtable
where map_id IN (1,2)
group by id
having COUNT(distinct map_id)=2 -- where this 2 is the number of items you want to find

This technique is called relational division.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • `having COUNT(*)=2` will do if a unique constraint was set on `map_id` for every `ID`, anyway `+1`. – John Woo Nov 27 '12 at 16:38