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=arch...@jab.org
