Reputation: 2783
I have a table with ids and values. they are only unique together.
ID VALUE
1 value1
1 value2
2 value1
3 value2
3 value3
3 value4
4 value3
5 value1
Assume I am given values "value2" and "value3", I want to select the ID that matches BOTH of these values (ie. 3)
I can
SELECT ID FROM table WHERE VALUE IN("value2", "value3")
However, it returns values that match either one as well. Is there a way to query ONLY those matching both of those values?
Upvotes: 1
Views: 55
Reputation: 96366
SELECT t1.id FROM table AS t1 JOIN table AS t2 ON t1.id=t2.id
WHERE t1.value="value2" AND t2.value="value3"
Upvotes: 1
Reputation: 11567
Try the following:
SELECT id FROM table WHERE value = "value2" and id in (SELECT id FROM table WHERE value = "value3")
Upvotes: 1
Reputation: 4995
You should use GROUP BY with HAVING
SELECT ID FROM table WHERE VALUE IN("value2", "value3") GROUP BY ID HAVING COUNT(*) = 2
Upvotes: 1