MySQL Self Join не все строки включены
У меня проблема с разработкой Self Join.
У меня есть следующая таблица:
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');
Затем мне нужно выполнить запрос, который выводит данные для каждого "элемента" в одной строке.
У меня есть запрос:
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`;
Проблема в том, что это возвращает только 2 строки (Группа 1 и Группа 2). Мне также нужно вернуть строку для Group=3, хотя для item=200 нет записи.
Как я могу сделать это, пожалуйста.
3 ответа
Решение
Поместите условие t2.item = 200 в предложение JOIN.
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`;
Избавьтесь от предложения where, переместите все в левое соединение:
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`;
РЕДАКТИРОВАТЬ: это (конечно) также даст вам строку, если только группа соответствует и ни 100, ни 200 не равны t1 (или t2)
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`
ВЫХОД
╔═══════╦═════════╦═════════╗
║ GROUP ║ ITEM100 ║ ITEM200 ║
╠═══════╬═════════╬═════════╣
║ 1 ║ aaa ║ bbb ║
║ 2 ║ ccc ║ ddd ║
║ 3 ║ eee ║ (null) ║
╚═══════╩═════════╩═════════╝