[TriLUG] OT: a SQL puzzler

[Aaron Schrab]

At 11:32 -0400 28 Apr 2010, Alan Porter <porter at trilug.org> 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?

SELECT
   races.id,
   races.race,
   first.winner AS first,
   second.winner AS second,
   third.winner AS third
FROM races
JOIN finishers AS first ON (races.id=first.id AND first.place = 1)
JOIN finishers AS second ON (races.id=second.id AND second.place = 2)
JOIN finishers AS third ON (races.id=third.id AND third.place = 3)
;