SELECT a few lines with WHERE

Question

PID VALUE 3 1 4 3 1 9 1 3 

How to select rows that have both 3 and 9? I tried

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

So, I get something like below, instead I get an empty set.

 PID 1 

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

Answer 1

The WHERE clause can evaluate conditions on only one row from a given table at a time. You cannot make a span condition multiple lines.

But you can use self-join to match multiple rows from one table to one row in the result set, so you can apply a condition that includes 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 different values:

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

Answer 2

This, I think, is a little more in line with what you need:

 select PID from table where VALUE IN (3, 9) group by PID having count(*) = 2 

It basically searches for entries that have either 3 or 9, groups them together and returns only those that have a count of two. I assume here (possibly incorrectly) that there can only be one 3 and one 9 per PID.

Answer 3

Like group by, you can also consider select different to return a unique set of pids.