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 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>

    </FONT>


  • 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.

     


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.