Select data from multiple rows
-
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?
-
<FONT color=#0000ff size=1>select</FONT><FONT size=1> A</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name
</FONT>
</FONT><FONT color=#0000ff size=1>from</FONT><FONT size=1> #consumed A
</FONT><FONT color=#0000ff size=1>where</FONT><FONT size=1> food </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Apple'
</FONT><FONT color=#808080 size=1> and</FONT><FONT size=1> </FONT><FONT color=#808080 size=1>exists</FONT><FONT size=1> </FONT><FONT color=#808080 size=1>(
</FONT><FONT color=#0000ff size=1> select </FONT><FONT color=#808080 size=1>*</FONT><FONT size=1>
<FONT color=#0000ff> </FONT></FONT><FONT color=#0000ff size=1>from</FONT><FONT size=1> #consumed B
</FONT><FONT color=#0000ff size=1> where</FONT><FONT size=1> A</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> B</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name
</FONT><FONT color=#808080 size=1><FONT color=#0000ff> </FONT>and</FONT><FONT size=1> B</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>food </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Shrimp'
</FONT><FONT color=#808080 size=1>)</FONT><FONT color=#808080 size=1>
-
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)
-
@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:
<FONT color=#0000ff size=1>SELECT</FONT><FONT size=1> A</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name
</FONT><FONT color=#0000ff size=1>FROM</FONT><FONT size=1> #consumed A
</FONT><FONT color=#808080 size=1>JOIN</FONT><FONT size=1> #consumed B
</FONT><FONT color=#0000ff size=1> ON</FONT><FONT size=1> A</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> B</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>name
</FONT><FONT color=#0000ff size=1>WHERE</FONT><FONT size=1> A</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>food </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Apple'
</FONT><FONT color=#808080 size=1> AND</FONT><FONT size=1> B</FONT><FONT color=#808080 size=1>.</FONT><FONT size=1>food </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Shrimp'</FONT>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...<FONT color=#ff0000 size=1>
</FONT>
-
This one's even faster (1/2 the processing of the first one), but it assumes that a single person cannot eat three apples...
<FONT color=#0000ff size=1>SELECT</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>name
FROM</FONT><FONT size=1> </FONT><FONT color=#808080 size=1>(
</FONT><FONT color=#0000ff size=1> SELECT</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>name</FONT><FONT color=#808080 size=1>,</FONT><FONT size=1> XXX </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>CASE</FONT><FONT size=1> food </FONT><FONT color=#0000ff size=1>WHEN</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Apple'</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>THEN</FONT><FONT size=1> 1 </FONT><FONT color=#0000ff size=1>WHEN</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'Shrimp'</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>THEN</FONT><FONT size=1> 2 </FONT><FONT color=#0000ff size=1>END
</FONT><FONT color=#0000ff size=1> FROM</FONT><FONT size=1> #consumed
</FONT><FONT color=#808080 size=1>)</FONT><FONT size=1> T
</FONT><FONT color=#0000ff size=1>GROUP</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>BY</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>NAME
HAVING</FONT><FONT size=1> </FONT><FONT color=#ff00ff size=1>SUM</FONT><FONT color=#808080 size=1>(</FONT><FONT size=1>XXX</FONT><FONT color=#808080 size=1>)</FONT><FONT size=1> </FONT><FONT color=#808080 size=1>=</FONT><FONT size=1> 3</FONT><FONT size=1>
</FONT>
-
Seems reminiscent of the following thread.
http://forums.worsethanfailure.com/forums/thread/121526.aspx
-
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.
-
Thanks! These suggestions worked out great.