I think the problem with your query is that you are using RIGHT OUTER JOINS when you can and should be using LEFT JOINS. You want to make sure you are always keeping the games regardless of the number of refs you find, so you left join the other tables to the games table. Basically saying keep the table on the left intact. I think that's what you are trying to do with the RIGHT OUTER JOIN. Try changing all your "RIGHT OUTER" to "LEFT".
Now, on to your design. You should just have one column for the referee. Your queries will be easier and faster and you won't have a limit to the number of referees you can have assigned. In your design, you will need to modified the table structure every time you need to support an extra ref. Plus, you have "empty spaces" in table when you have less than 4 refs. And what if you want to find out which games a ref is assigned to? You need to query 4 columns.
You can use the GROUP_CONCAT function to get everything in one row.
On May 19, 2005, at 8:35 PM, Andy Green wrote:
I'm trying to write a query that pulls details on a game record, as well as
the officials assigned to the game (up to 4 officials may be assigned to
each game, but that's not always the case).
Game details are in the games table, and assignments are in the games_referees table (which I alias as referee,ar1,ar2, and fourth).
Ultimately, I want all the games for a given date, and the referees assigned
to them. Below is the query I'm working with so far. In its current state,
it returns results only when a full crew is assigned to the game (referee,
ar1,ar2, fourth). The query is below:
SELECT g. * , concat( ref.fname, ' ', ref.lname ) AS ref, concat(
ar1.fname, ' ', ar1.lname ) AS ar1, concat( ar2.fname, ' ', ar2.lname )
AS ar2, concat( fourth.fname, ' ', fourth.lname ) AS fourth
FROM ( ( ( ( ( ( ( ( games g
RIGHT OUTER JOIN games_referees ref_ass ON ( g.id = ref_ass.gnum ) )
RIGHT OUTER JOIN people ref ON ( ref.login = ref_ass.referee ) )
RIGHT OUTER JOIN games_referees ar1_ass ON ( g.id = ar1_ass.gnum ) )
RIGHT OUTER JOIN people ar1 ON ( ar1.login = ar1_ass.referee ) )
RIGHT OUTER JOIN games_referees ar2_ass ON ( g.id = ar2_ass.gnum ) )
RIGHT OUTER JOIN people ar2 ON ( ar2.login = ar2_ass.referee ) )
RIGHT OUTER JOIN games_referees fourth_ass ON ( g.id = fourth_ass.gnum )
)
RIGHT OUTER JOIN people fourth ON ( fourth.login = fourth_ass.referee ) )
WHERE ref_ass.position =1 AND ar1_ass.position =2 AND ar2_ass.position =3
AND fourth_ass.position =4 AND g.date = '2004-09-25'
Any help would be greatly appreciated.
Thank
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
