php - SELECT multiple rows with WHERE -

pid       value 3        1 4        3 1        9 1        3

how select row(s) has both values 3 , 9? tried

select pid table value = 3 , value = 9

so below, instead empty set.

pid       1

pid 4 should not included in result because not have value 9

the clause can evaluate conditions against one row given table @ time. can't make condition span multiple rows.

but can use self-join match multiple rows same table 1 row of result set, can apply condition involves both.

select t1.pid table t1 join table t2 on t1.pid=t2.pid t1.value = 3 , t2.value = 9;

an alternative solution use group , count distinct values:

select t.pid table t t.value in (3,9) group t.pid having count(distinct t.value) = 2;

Barbera