> > 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. One relatively simple way of dealing with a situation like this, presuming that your tables look something like: [games] Recid, gameid, gamedesc, gamedate 1, 1, 'Game 1', '2005-01-01 00:00:00' 2, 2, 'Game 2', '2005-01-01 00:00:00' 3, 3, 'Game 3', '2005-01-02 00:00:00' 4, 4, 'Game 4', '2005-01-03 00:00:00' [refs] Recid, gameid, refname 1, 1, 'ref 1' 2, 1, 'ref 2' 3, 1, 'ref 3' 4, 2, 'ref 4' 5, 2, 'ref 5' 6, 2, 'ref 6' 7, 2, 'ref 7' 8, 3, 'ref 1' 9, 3, 'ref 7' 10, 3, 'ref 8' 11, 4, 'ref 8' ...would be to use the following query: select g.gameid, g.gamedate, g.gamedesc, group_concat(r.refname order by r.refname) from games g join refs r on g.gameid = r.gameid group by r.gameid This makes use of mysql's group_concat() aggregate function to produce a recordset like: Gameid, gamedate, gamedesc, reflist 1, '2005-01-01 00:00:00', 'Game 1', 'ref 1,ref 2,ref 3' 2, '2005-01-01 00:00:00', 'Game 2', 'ref 4,ref 5,ref 6,ref 7' 3, '2005-01-02 00:00:00', 'Game 3', 'ref 1,ref 7,ref 8' 4, '2005-01-03 00:00:00', 'Game 4', 'ref 8' Then you would simply use PHP's explode() function on the reflist field of each record to populate an array with the names of the referees of each game. Note: I believe group_concat() is specific to MySQL and is only available in versions 4.1.x and above. Hope this is of some help. Murray -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
