Get matrix table from union in mysql

I have several tables structured as shown below.

Master table

CREATE TABLE `master` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

id  code
1   100050
2   100051
3   100052

      

First Mapping Table

CREATE TABLE `mappings_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_code` int(11) NOT NULL,
  `mappings_one_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `master_code` (`master_code`),
  KEY `mappings_one_code` (`mappings_one_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id  master_code mappings_one_code
1   100050      2346
2   100051      1267
3   100051      3890
4   100052      5698

      

Second Mapping Table

CREATE TABLE `mappings_two` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_code` int(11) NOT NULL,
  `mappings_two_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `master_code` (`master_code`),
  KEY `mappings_two_code` (`mappings_two_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id  master_code mappings_two_code
1   100050      BE45
2   100050      HO87
3   100051      YT67
4   100051      AX56

      

And there are so many mapping tables associated with the master table by the master_code field. I would like to find the result as below:

id  master_code mappings_one_code   mappings_two_code
1   100050      2346                BE45
2   100050      null                HO87
3   100051      1267                YT67
4   100051      3890                AX56
5   100052      5698                null

      

I tried below query but was unable to get the desired result as above. Can anyone help me solve the same?

SELECT 
  m.`code`,m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code',
  m2.`mappings_two_code` AS 'mappings_two_code'
FROM 
  master m LEFT JOIN 
  mappings_one m1 ON m1.`master_code` = m.`code` LEFT JOIN 
  mappings_two m2 ON m2.`master_code` = m.`code`

      

+3


source to share


1 answer


Unfortunately mysql doesn't support FULL OUTER JOIN

, so you need something like:

http://sqlfiddle.com/#!9/127f4/1



SELECT m3.code, m2.u_idx, m3.name, m3.mappings_one_code, m2.mappings_two_code 
FROM (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code'
FROM `master` m 
LEFT JOIN 
  mappings_one m1 
ON m1.`master_code` = m.`code` 
) m3
LEFT JOIN 
  (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_two_code` AS 'mappings_two_code'
FROM `master` m 
LEFT JOIN 
  mappings_two m1 
ON m1.`master_code` = m.`code` 
) m2
on m3.u_idx = m2.u_idx
UNION
SELECT m2.code, m2.u_idx, m2.name, m3.mappings_one_code, m2.mappings_two_code 
FROM (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code'
FROM `master` m 
LEFT JOIN 
  mappings_one m1 
ON m1.`master_code` = m.`code` 
) m3
RIGHT JOIN 
  (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_two_code` AS 'mappings_two_code'
FROM `master` m 
LEFT JOIN 
  mappings_two m1 
ON m1.`master_code` = m.`code` 
) m2
on m3.u_idx = m2.u_idx

ORDER BY u_idx

      

+2


source







All Articles