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
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
