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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with a tricky query
First, your design could be better. You are storing the same data (referee) in multiple columns. More on that later. I think the problem with your query is that you are using RIGHT OUTER JOINS when you can and should be using LEFT JOINS. You want to make sure you are always keeping the games regardless of the number of refs you find, so you left join the other tables to the games table. Basically saying keep the table on the left intact. I think that's what you are trying to do with the RIGHT OUTER JOIN. Try changing all your RIGHT OUTER to LEFT. Now, on to your design. You should just have one column for the referee. Your queries will be easier and faster and you won't have a limit to the number of referees you can have assigned. In your design, you will need to modified the table structure every time you need to support an extra ref. Plus, you have empty spaces in table when you have less than 4 refs. And what if you want to find out which games a ref is assigned to? You need to query 4 columns. You can use the GROUP_CONCAT function to get everything in one row. On May 19, 2005, at 8:35 PM, Andy Green wrote: 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Need help with a tricky query
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
