Re: How to get hanging 1:M table rows as single column in main query?
GROUP_CONCAT() ? And group by id_fmr ? JW On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent dae...@daevid.com wrote: Given three basic tables. An fmr table which has Field Maintenance Reports, a Seat table and a hanging or glue table to map Seats to FMRs. [See below] How do I get all the Seats to be in a single row with the FMR data? If I make this kind of query, they come in as separate rows: SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, `seat` FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3; id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C 3 320237274 2333 JFK 2010-09-24 04:35:31 35D 3 320237274 2333 JFK 2010-09-24 04:35:31 35E I want something more like: id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E Now, I'm going to be showing a few thousand FMR rows (and ideally their seats). What I do now is use PHP to pull the FMR records that match a certain criteria/filter. Then I pull in the entire dim_seats as an array and store it in a session since it's not going to change ever. Then I loop over all the id_fmr that I have pulled and look up in the fmr_has_seat table by id_fmr and implode() the seats from the session array. It saves me a few joins and gets the job done, but I keep feeling like there's a better way to do it. I'm thinking there's some magic with a subselect and concat or something in SQL, but then I wonder if that's any more efficient as mySQL still has to do two SELECTs per FMR row. This feels to me like a common problem and there must be an optimal mySQL way of doing it. Hanging tables of 1:M relationships are used everywhere. === == CREATE TABLE `fmr` ( `id_fmr` int(11) NOT NULL auto_increment, `fmr_number` varchar(32) NOT NULL default '', `fmr_system` smallint(6) default NULL, `fmr_station` varchar(4) NOT NULL default '', `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id_fmr`) ) ENGINE=InnoDB id_fmr fmr_number fmr_system fmr_station created_ts -- -- -- --- --- 1 319235F2A 2333 JFK 2010-09-24 04:35:31 2 319235F29 2333 JFK 2010-09-24 04:35:31 3 320237274 2333 JFK 2010-09-24 04:35:31 --- 4 32023726D 2333 JFK 2010-09-24 04:35:31 5 32023725A 2333 JFK 2010-09-24 04:35:31 6 32023724F 2333 JFK 2010-09-24 04:35:31 7 320237241 2333 LAX 2010-09-24 04:35:31 8 32023723A 2333 LAX 2010-09-24 04:35:31 9 320237232 2333 JFK 2010-09-24 04:35:31 10 320237230 2333 JFK 2010-09-24 04:35:31 .... .. .. .. CREATE TABLE `fmr_has_seat` ( `id_fmr` int(11) NOT NULL auto_increment, `id_dim_seat` int(10) unsigned NOT NULL, PRIMARY KEY (`id_fmr`,`id_dim_seat`), KEY `id_dim_seat` (`id_dim_seat`), CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB id_fmr id_dim_seat -- --- 3 888 --- 3 889 --- 3 890 --- 4 422 4 423 4 551 4 552 4 553 5 420 5 550 5 628 5 629 5 706 5 707 5 811 ... ... CREATE TABLE `dim_seat` ( `id_dim_seat` int(10) unsigned NOT NULL auto_increment, `seat` varchar(4) default NULL, PRIMARY KEY (`id_dim_seat`), KEY `seat` (`seat`) ) ENGINE=InnoDB id_dim_seat seat --- -- ... ... 888 35C --- 889 35D --- 890 35E --- 891 35F 892 35G ... ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: How to get hanging 1:M table rows as single column in main query?
BRILLIANT SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, GROUP_CONCAT(`seat`) FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3 GROUP BY id_fmr; id_fmr fmr_number fmr_system fmr_station created_ts group_concat(`seat`) -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, September 29, 2010 1:35 PM To: Daevid Vincent Cc: MySQL Subject: Re: How to get hanging 1:M table rows as single column in main query? GROUP_CONCAT() ? And group by id_fmr ? JW On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent dae...@daevid.com wrote: Given three basic tables. An fmr table which has Field Maintenance Reports, a Seat table and a hanging or glue table to map Seats to FMRs. [See below] How do I get all the Seats to be in a single row with the FMR data? If I make this kind of query, they come in as separate rows: SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, `seat` FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3; id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C 3 320237274 2333 JFK 2010-09-24 04:35:31 35D 3 320237274 2333 JFK 2010-09-24 04:35:31 35E I want something more like: id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E Now, I'm going to be showing a few thousand FMR rows (and ideally their seats). What I do now is use PHP to pull the FMR records that match a certain criteria/filter. Then I pull in the entire dim_seats as an array and store it in a session since it's not going to change ever. Then I loop over all the id_fmr that I have pulled and look up in the fmr_has_seat table by id_fmr and implode() the seats from the session array. It saves me a few joins and gets the job done, but I keep feeling like there's a better way to do it. I'm thinking there's some magic with a subselect and concat or something in SQL, but then I wonder if that's any more efficient as mySQL still has to do two SELECTs per FMR row. This feels to me like a common problem and there must be an optimal mySQL way of doing it. Hanging tables of 1:M relationships are used everywhere. == = == CREATE TABLE `fmr` ( `id_fmr` int(11) NOT NULL auto_increment, `fmr_number` varchar(32) NOT NULL default '', `fmr_system` smallint(6) default NULL, `fmr_station` varchar(4) NOT NULL default '', `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id_fmr`) ) ENGINE=InnoDB id_fmr fmr_number fmr_system fmr_station created_ts -- -- -- --- --- 1 319235F2A 2333 JFK 2010-09-24 04:35:31 2 319235F29 2333 JFK 2010-09-24 04:35:31 3 320237274 2333 JFK 2010-09-24 04:35:31 --- 4 32023726D 2333 JFK 2010-09-24 04:35:31 5 32023725A 2333 JFK 2010-09-24 04:35:31 6 32023724F 2333 JFK 2010-09-24 04:35:31 7 320237241 2333 LAX 2010-09-24 04:35:31 8 32023723A 2333 LAX 2010-09-24 04:35:31 9 320237232 2333 JFK 2010-09-24 04:35:31 10 320237230 2333 JFK 2010-09-24 04:35:31 .... .. .. .. CREATE TABLE `fmr_has_seat` ( `id_fmr` int(11) NOT NULL auto_increment, `id_dim_seat` int(10) unsigned NOT NULL, PRIMARY KEY (`id_fmr`,`id_dim_seat`), KEY `id_dim_seat` (`id_dim_seat`), CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB id_fmr id_dim_seat -- --- 3 888 --- 3 889 --- 3 890 --- 4 422 4 423 4 551
