[TriLUG] OT: a SQL puzzler

[Kevin Hunter]

At 11:32am -0400 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.

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

Your 'first', 'second', and 'third' columns come from multiple rows in
your finishers table.  Though this is possible to do with SQL, may I ask
why you don't do this in your application?  There are of course good
reasons for either choice, just make sure they fit for you.  Enter some
pithy quote about "right tool, right job" and all that.

However, answering the actual question ... I might suggest using a view:

CREATE VIEW winner_list AS
SELECT
	r.id,
	r.race,
	w1.winner AS First,
	w2.winner AS Second,
	w3.winner AS Third
FROM
	races r,
	finishers w1,
	finishers w2,
	finishers w3
WHERE
    r.id = w1.id
AND r.id = w2.id
AND r.id = w3.id
AND w1.place = 1
AND w2.place = 2
AND w3.place = 3;

This has the advantage of being much clearer in use, and lets you have
code document code:

 =# SELECT * FROM winner_list ;
 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
(3 rows)

Cheers,

Kevin