Reputation: 2783

mysql properly construct a select query

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

Answers (3)

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