How to get hanging 1:M table rows as single column in main query?

am 29.09.2010 21:38:07 von Daevid Vincent

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=gcdmg-mysql-2@m.gmane.org

Re: How to get hanging 1:M table rows as single column in main query?

am 29.09.2010 22:34:36 von Johnny Withers

--001485f44fc0d430ad04916be31a
Content-Type: text/plain; charset=ISO-8859-1

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=johnny@pixelated.net
>
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f44fc0d430ad04916be31a--

RE: How to get hanging 1:M table rows as single column in main query?

am 29.09.2010 23:36:35 von Daevid Vincent

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@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 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=johnny@pixelated.net
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org