MySQL Self Join Not All Rows included

Question

I have a problem working out a Self Join.

I have the following table:

CREATE TABLE  `test`.`tableN` (
`id` int(10) unsigned NOT NULL auto_increment,  
`Group` int(10) unsigned NOT NULL,  
`Item` int(10) unsigned NOT NULL,
`data` varchar(45) default NULL,
PRIMARY KEY  (`id`)
) 
ENGINE=InnoDB;

INSERT INTO `test`.`tableN` (`Group`,`Item`,`data`)
VALUES 
(1,100,'aaa'),
(1,200,'bbb'),
(2,100,'ccc'),
(2,200,'ddd'),
(3,100,'eee');

What I then need to do is run a query, that outputs the data for each 'item' in a single Row.

The Query I have got is:

SELECT
t1.`Group`,
t1.`item`,
t1.`data`,
t2.`item`,
t2.`data`
FROM tableN t1
LEFT JOIN tableN t2 ON t2.`group`=t1.`group`
WHERE 1=1
AND t1.item = 100
AND t2.item = 200
GROUP BY t1.`Group`;

The problem is, this only returns 2 rows (Group 1 and Group 2). I need it to also return a row for Group=3, even though there is no entry for item=200.

How can I do this please.

score 2 · Answer 1 · answered Apr 05 '13 at 08:54

Get rid of your where clause, move it all to your left join:

SELECT
    t1.`Group`,
    t1.`item`,
    t1.`data`,
    t2.`item`,
    t2.`data`
  FROM `test`.`tableN` AS t1
  LEFT JOIN `test`.`tableN` t2 ON t2.`group`=t1.`group` AND t1.item = 100 AND t2.item = 200
  GROUP BY t1.`Group`;

Fiddle

EDIT: this would (of course) also get you a row if just the group matches and neither 100 nor 200 equals t1 (or t2)

score 1 · Answer 2 · answered Apr 05 '13 at 08:50

SELECT  `GROUP`,
        MAX(CASE WHEN Item = 100 THEN data END) Item100,
        MAX(CASE WHEN Item = 200 THEN data END) Item200
FROM    TableN
WHERE   Item IN (100, 200)
GROUP   BY `GROUP`

SQLFiddle Demo

OUTPUT

╔═══════╦═════════╦═════════╗
║ GROUP ║ ITEM100 ║ ITEM200 ║
╠═══════╬═════════╬═════════╣
║     1 ║ aaa     ║ bbb     ║
║     2 ║ ccc     ║ ddd     ║
║     3 ║ eee     ║ (null)  ║
╚═══════╩═════════╩═════════╝

score 1 · Accepted Answer · answered Apr 05 '13 at 08:51

1

Put the t2.item = 200 condition into JOIN clause.

SELECT
t1.`Group`,
t1.`item`,
t1.`data`,
t2.`item`,
t2.`data`
FROM tableN t1
LEFT JOIN tableN t2 ON t2.`group`=t1.`group` AND t2.item = 200
WHERE 1=1
AND t1.item = 100
GROUP BY t1.`Group`;

answered Apr 05 '13 at 08:51

fancyPants

50,732
33
89
96

MySQL Self Join Not All Rows included

3 Answers3