[TriLUG] OT: a SQL puzzler

[William Sutton]

err... lazy me, try this instead:

SELECT r.id, r.name,
        (SELECT f.winner
          FROM finishers f
          WHERE f.id=r.id AND f.place=1
        ) AS first,
        (SELECT f.winner
          FROM finishers f
          WHERE f.id=r.id AND f.place=2
        ) AS second,
        (SELECT f.winner
          FROM finishers f
          WHERE f.id=r.id AND f.place=3
        ) AS third
   FROM races r
   ORDER BY r.id


William Sutton

On Wed, 28 Apr 2010, William Sutton wrote:

> like this:?
>
> SELECT r.id, r.name,
>       (SELECT f.winner
>         FROM finishers f
>         WHERE f.id=r.id AND f.place=1
>       ) AS first
>       (SELECT f.winner
>         FROM finishers f
>         WHERE f.id=r.id AND f.place=2
>       ) AS first
>       (SELECT f.winner
>         FROM finishers f
>         WHERE f.id=r.id AND f.place=3
>       ) AS first
>  FROM races r
>  ORDER BY r.id
>
>
> William Sutton
>
> On Wed, 28 Apr 2010, Alan Porter wrote:
>
>> 
>> I have a SQL puzzler.  I am trying to join two tables that have a
>> one-to-many relationship, but I know in advance how many is 'many'.
>> 
>> I specifically want the result to give three rows -- not nine rows.
>> 
>> Anyone want to take a stab at this?
>> 
>> 
>> 'races' table
>> +-----+---------------------+
>> | id  | race                |
>> +-----+---------------------+
>> | 101 | 1992 Pres Election  |
>> | 102 | 2007 Kentucky Derby |
>> | 103 | 2009 Daytona 500    |
>> +-----+---------------------+
>> 
>> 'finishers' table
>> +-----+-------+---------------+
>> | id  | place | winner        |
>> +-----+-------+---------------+
>> | 101 | 1     | Bill Clinton  |
>> | 101 | 2     | G.H.W. Bush   |
>> | 101 | 3     | Ross Perot    |
>> | 102 | 1     | Street Sense  |
>> | 102 | 2     | Hard Spun     |
>> | 102 | 3     | Curlin        |
>> | 103 | 1     | Matt Kenseth  |
>> | 103 | 2     | Kevin Harvick |
>> | 103 | 3     | Allmendinger  |
>> +-----+-------+---------------+
>> 
>> results
>> +-----+---------------------+--------------+---------------+--------------+
>> | ID  | RACE                | FIRST        | SECOND        | THIRD        |
>> +-----+---------------------+--------------+---------------+--------------+
>> | 101 | 1992 Pres Election  | Bill Clinton | G.H.W. Bush   | Ross Perot   |
>> | 102 | 2007 Kentucky Derby | Street Sense | Hard Spun     | Curlin       |
>> | 103 | 2009 Daytona 500    | Matt Kenseth | Kevin Harvick | Allmendinger |
>> +-----+---------------------+--------------+---------------+--------------+
>> 
>> 
>> Alan
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> .
>> -- 
>> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
>> TriLUG FAQ          : http://www.trilug.org/wiki/Frequently_Asked_Questions
>> 
> -- 
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG FAQ          : http://www.trilug.org/wiki/Frequently_Asked_Questions
>