List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 29 2010 9:36pm
Subject:RE: How to get hanging 1:M table rows as single column in main query?
View as plain text  
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:johnny@stripped] 
> 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 
> <daevid@stripped> 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=1
> >
> >
> 
> 
> -- 
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
> 

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