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
