using LEFT JOIN to join 1 to N tables - Howto?

[Mike Curwen]

Say I have something like:

CREATE TABLE `prize` 
(
`prize_id` bigint (20) NOT NULL  AUTO_INCREMENT , 
`sponsor_id` bigint (20), 
`location_id` bigint (20), 
`prize_name` varchar (255), 
`prize_winner_id` bigint (20), 
PRIMARY KEY (prize_id)
)
#
#
CREATE TABLE `uzer` 
(
`uzer_id` bigint (20) NOT NULL  AUTO_INCREMENT , 
`uzer_name` varchar (255), 
PRIMARY KEY (uzer_id)
)
#
#
CREATE TABLE `sponsor` 
(
`sponsor_id` bigint (20) NOT NULL  AUTO_INCREMENT , 
`sponsor_name` varchar (255), 
PRIMARY KEY (sponsor_id)
)
#
#
CREATE TABLE `location` 
(
`location_id` bigint (20) NOT NULL  AUTO_INCREMENT , 
`location_name` varchar (255), 
PRIMARY KEY (location_id)
)

What I really care about is, I want a result of all prizes, regardless of whether a winner has been assigned.

SELECT p.*, u.uzer_name 
FROM   prize as p LEFT JOIN uzer as u  ON (p.prize_winner_id=u.uzer_id)

Now how do I retrieve the other 'named' values for the other foreign keys in 'prize' ?
Neither of the following work:

SELECT p.location_id, l.location_name, p.sponsor_id, s.sponsor_name, p.prize_id, p.prize_name, u.uzer_name
FROM   prize as p LEFT JOIN uzer as u, location as l, sponsor as s  
ON (p.prize_winner_id=u.uzer_id) AND (p.sponsor_id=s.sponsor_id) AND (p.location_id=l.location_id)
.
.
.
.
SELECT p.location_id, l.location_name, p.sponsor_id, s.sponsor_name, p.prize_id, p.prize_name, u.uzer_name
FROM   (prize as p LEFT JOIN uzer as u ON p.prize_winner_id=u.uzer_id),
       (prize as p2 LEFT JOIN location as l ON p2.location_id=l.location_id), 
       (prize as p3 LEFT JOIN sponsor as s  ON p.sponsor_id=s.sponsor_id)

Here's what doesn't generate an error, but what *does* happen is that rows are dropped out,
when there are no matching rows in the sponsor and location tables. (which should never happen,
but if it *does*, i still want the query to return unmatched rows).

SELECT p.location_id, l.location_name, p.sponsor_id, s.sponsor_name, p.prize_id, p.prize_name, u.uzer_name
FROM location as l, sponsor as s, (prize as p LEFT JOIN uzer as u ON p.prize_winner_id=u.uzer_id
WHERE p.location_id=l.location_id
AND   p.sponsor_id=s.sponsor_id

So that last one doesn't fail, but it also drops rows, which was the whole point of using LEFT JOIN (except
I can't seem to figure out how to get 1->N joins with LEFT JOIN).

Help!

p.s. MySql, so no subqueries!

[Mike Curwen]

ha. I've done this far too many times already.

The answer is:

SELECT p.location_id, l.location_name, p.sponsor_id, s.sponsor_name, p.prize_id, p.prize_name, u.uzer_name
FROM
((prize as p LEFT JOIN uzer as u ON p.prize_winner_id=u.uzer_id)
  LEFT JOIN location as l ON p.location_id=l.location_id)
    LEFT JOIN sponsor as s  ON p.sponsor_id=s.sponsor_id