Select data from multiple rows

iAmNotACantalope

I am by no means a database expert, but a coworker asked for help on this problem and I can't figure it out:

Table: Consumed

Columns: [Name : varchar ] [ Food : varchar ]

Example data:

John Apple

John Pear

John Shrimp

Karl Pineapple

Karl Apple

Jimmy Steak

Jimmy Apple

Jimmy Shrimp

We need to get a list of the people who ate both an apple and some shrimp (Jimmy and John in the example). It sounds simple, but I simply can't figure it out. How do I write the SQL query?

RaspenJho

select A.name
from #consumed A
where food = 'Apple'
 and exists (
 select *
 from #consumed B
 where A.name = B.name
 and B.food = 'Shrimp'
)

PSWorx

Wouldn't
[code]SELECT A.name FROM consumed A, consumed B WHERE A.food = 'Apple' AND B.food = 'Shrimp' AND A.name = B.name[/code]
work too? (not a DB expert either)

RaspenJho

@PSWorx said:

Wouldn't
[code]SELECT A.name FROM consumed A, consumed B WHERE A.food = 'Apple' AND B.food = 'Shrimp' AND A.name = B.name[/code]
work too? (not a DB expert either)

Yes.

That's the old-school syntax though... You'd do this using ANSI-92 syntax:

SELECT A.name
FROM #consumed A
JOIN #consumed B
 ON A.name = B.name
WHERE A.food = 'Apple'
 AND B.food = 'Shrimp'

But if you compare the query plans between the original I posted and the latest one, you'll see that the JOIN option takes 3 times the processing...

RaspenJho

This one's even faster (1/2 the processing of the first one), but it assumes that a single person cannot eat three apples...

SELECT name
FROM (
 SELECT name, XXX = CASE food WHEN 'Apple' THEN 1 WHEN 'Shrimp' THEN 2 END
 FROM #consumed
) T
GROUP BY NAME
HAVING SUM(XXX) = 3

lpope187

Seems reminiscent of the following thread.

http://forums.worsethanfailure.com/forums/thread/121526.aspx

RaspenJho

It's subtely different. In this one, the OP did not need to return the values "Apple" and "Shrimp", the other thread was ambiguous to that point.

iAmNotACantalope

Thanks! These suggestions worked out great.