RE: [PHP-DB] Need help with a tricky query

> 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. Hi Andy, If no-one manages to find a solution for you right away, could you please supply some pseudo-data from the tables you are working with. Also, which db server application and version are you working with? I'm sure a solution can be found, but I for one would be closer to helping you find it if I had a better idea of the structure of the tables involved and the data they contain. Regards, Murray