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

Wow people really don't read these questions for actual meaning do they? I'd use a `group by PID`, a `where value in (3, 9)`, and a `having count(*) = 2`. — Chris Eberle, Jul 01 '11 at 20:45
Amazing how many people don't know what "both" means. Though, to be fair, the OP's use of the word "row" is misleading. — Lightness Races in Orbit, Jul 01 '11 at 21:27

Bill Karwin · Accepted Answer · 2011-07-01T20:56:12.703

8

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;

edited Jul 01 '11 at 20:56

answered Jul 01 '11 at 20:45

Bill Karwin

538,548
86
673
828

good answer - not enough detailin question but answer will change if bigger amount of data in tables – Simon Thompson Jul 01 '11 at 20:50
I'd say to go with the GROUP BY method, since there's no guarantee of join order for the first; i.e., t1.value may be 9 and t2.value may be 3. – Paul Sonier Jul 01 '11 at 21:05
@Paul Sonier: The WHERE clause makes sure t1 *does* match the rows where value is 3, and t2 *does* match the rows where value is 9. That is, it examines all rows until it finds those that match the condition. – Bill Karwin Jul 01 '11 at 21:26

score 1 · Answer 2 · answered Jul 01 '11 at 20:47

1

This, I think is a little more along the lines of what you're after:

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

Basically this looks for entries that have either a 3 or a 9, groups them together, and returns only the ones that have a count of two. I'm assuming here (perhaps incorrectly) that there can only be one 3 and one 9 per PID.

answered Jul 01 '11 at 20:47

Chris Eberle

47,994
12
82
119

1

Right, that's usually not a safe assumption. If two rows both have value 9, COUNT(*) will return 2, even though no row exists with value 3. – Bill Karwin Jul 01 '11 at 20:49
Fair enough, still it should give the OP a general direction. I'd correct it using DISTINCT but that solution has already been proposed so I'll just keep this as is. – Chris Eberle Jul 01 '11 at 20:53
1

Yep, it depends on the table. If there's a UNIQUE constraint over (pid,value) then your solution would work fine. – Bill Karwin Jul 01 '11 at 20:54

score 0 · Answer 3 · answered Jul 01 '11 at 20:51

0

As well as 'group by' you could also consider 'select distinct' to return a unique set of pids.

answered Jul 01 '11 at 20:51

Kevin Burton

11,676
2
24
37

SELECT multiple rows with WHERE

3 Answers3