CODEWITHSUNDEEP
mysqljoin
Lipi Solanki
Lipi Solanki

Reputation: 31

Get matrix table from join in mysql

I have multiple tables structured as 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 so many mappings tables related with master table by master_code field. I would like to find result like 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 with below query but not able to get desired result as above. Can anyone guide me to 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`

Upvotes: 3

Views: 104

Answers (1)

Alex
Alex

Reputation: 17289

Unfortunately mysql does not 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

Upvotes: 2

Related Questions