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