[TriLUG] OT: a SQL puzzler

[Alan Porter]

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







.