SELECT multiple rows with WHERE

Question

PID       VALUE
3        1
4        3
1        9
1        3

How to select row(s) that has both values 3 and 9? I tried

select PID from table where VALUE = 3 and VALUE = 9

So that i get something like below, instead i get an empty set.

PID      
1       

PID 4 should not be included in the result because it do not have VALUE 9

Answer 1

The WHERE clause can only evaluate conditions against one row from a given table at a time. You can't make a condition span multiple rows.

But you can use a self-join to match multiple rows from the same table into one row of the result set, so you can apply a condition that involves both.

SELECT t1.pid
FROM table t1 JOIN table t2 ON t1.pid=t2.pid
WHERE t1.value = 3 AND t2.value = 9;

An alternative solution is to use GROUP BY and count the distinct values:

SELECT t.pid
FROM table t
WHERE t.value IN (3,9)
GROUP BY t.pid
HAVING COUNT(DISTINCT t.value) = 2;