[TriLUG] OT: a SQL puzzler

[Michael Peters]

On 04/28/2010 11:40 AM, 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

Basic idea is right, syntax is wrong:
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

That's using sub-queries. Using multiple joins it's like this:

SELECT r.id, r.name, first.name, second.name, third.name
FROM races r
JOIN finishers first  ON (first.id = r.id AND first.place = 1)
JOIN finishers second ON (second.id = r.id AND second.place = 2)
JOIN finishers third  ON (third.id r.id AND third.place = 3)
ORDER BY r.id

Which performs better will be up to your particular DB, your data set 
size and might even vary between versions of that DB.

-- 
Michael Peters
Plus Three, LP