[TriLUG] OT: a SQL puzzler

[William Sutton]

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
>