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 > 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
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 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
