List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 29 2010 7:38pm
Subject:How to get hanging 1:M table rows as single column in main query?
View as plain text  
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
        ...  ...    

Thread
How to get hanging 1:M table rows as single column in main query?Daevid Vincent29 Sep
  • Re: How to get hanging 1:M table rows as single column in main query?Johnny Withers29 Sep
    • RE: How to get hanging 1:M table rows as single column in main query?Daevid Vincent29 Sep