| Andy Green 2005-05-26, 8:56 pm |
| 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
|