Почему не верно работает mysql left join?

Question

[Валерий Чупурнов]

Добрый день
Есть две таблицы
plugins

id name
1   first
2   second

и images

id pluginid image
1  1            1.jpg
2  1            4.png
3  2            10.png

Далее нужно вытянуть все плагины с их первыми фото. делаю такой запрос

select jp.*, ji.image
from jml_jquery_plugins as jp 
left join jml_jquery_images as ji 
	on ji.pluginid=jp.id 
where jp.del<>1 and jp.lastupdate>0 
order jp.name asc 
limit 20

в результате, запрос возвращает все записи из таблицы plugins, дублированные с таблицей images. Т.е. декартово произведение

id name image
1   first  1.jpg
1   first  4.png
2   second 10.png

ведь left join должен работать не так

на данный момент работает точно также как

select jp.*, ji.image
from jml_jquery_plugins as jp , jml_jquery_images as ji 
where jp.del<>1 and jp.lastupdate>0  and ji.pluginid=jp.id
order jp.name asc 
limit 20

Comments

[Immortal_pony]

И как же должен работать LEFT JOIN?

[Валерий Чупурнов]

Immortal_pony: вернуть все записи из таблицы 1 и если есть соответствующие то из таблицы 2

[Валерий Чупурнов]

Immortal_pony: если нет, то null. Разве нет?

[Immortal_pony]

Если соответствий более одного, то вернется более одной строки.
Чтобы этого избежать, можно использовать GROUP BY.

Answer 1

[Тимофей]

Точно именно декартово произведение? Тогда должно вернуться 6 строк, но это если бы не было условия ON, у тебя же оно есть и составлено вроде бы правильно.
У тебя тут по идее должно вернуться 3 строки, по одной на каждый найденный image. Данные будут дублироваться в данном случае только для pluginid = 1, так как там 2 картинки, и это нормально. Чтобы этого избежать, можно добавить GROUP BY jp.id и SELECT GROUP_CONCAT(ji.image SEPARATOR ',') as images, тогда у тебя вернется строка с картинками через запятую и количество строк на выходе будет равным количеству plugins.

Comments

[Валерий Чупурнов]

мне не нужны все картинки. Мне нужны все плагины, и если есть фото для плагина, хоть одно, то в отдельном поле его. При этом количество записей должно быть таким же как в plugins

[Валерий Чупурнов]

про group by знаю, но ведь left join должен работать и без него. Берем все записи из plugins и в поле image подставляем либо null либо фото из images

[Immortal_pony]

SELECT 
    jp.*, 
    ji.`image`
FROM
    jml_jquery_plugins AS jp LEFT JOIN 
    jml_jquery_images AS ji ON ji.`pluginid`=jp.`id`
WHERE 
    jp.`del`!=1 AND 
    jp.`lastupdate`>0
GROUP BY jp.`id` -- новая строка 
ORDER BY jp.`name` ASC 
LIMIT 20

[Тимофей]

Left join соединяет строки из двух таблиц. Как ты себе представляешь соединение таблиц при отношении один-ко-многим, чтобы при этом получалось по одной строке из исходной таблицы? Left join работает не так, как ты думаешь. Он дублирует записи из исходной таблицы по количеству записей из присоединенной. Я поэтому и говорю, что в твоем случае нужен GROUP BY, иначе будут дубли, и это нормальное поведение базы. Если тебе не нужны сами картинки, а только их наличие, то можешь вместо конкатенации написать COUNT(ji.id) as count, например, и потом проверять на count > 0.

[Валерий Чупурнов]

Тимофей: вот ведь, уверен что тысячу раз до этого делал такие запросы. И что никак без group подобный запрос не сделать?

[Тимофей]

Сделать. JOIN с подзапросом. Думаешь, так проще?)

[Валерий Чупурнов]

Тимофей: не думаю, но если group будет занят, то придется что-то придумывать.

[Тимофей]

Не совсем понял, что значит "group будет занят"? На самом деле я невнимательно прочитал твой вопрос, поэтому поправка небольшая: если тебе нужно выбрать именно первую картинку, то без подзапроса не обойтись.
SELECT jp.*, ji.image as first_image
FROM jpl_jquery_plugins jp
LEFT JOIN (
SELECT MIN(id) as id FROM jml_jquery_images WHERE pluginid = jp.id GROUP BY pluginid
) ji_first_ids
LEFT JOIN jml_jquery_images ji ON ji_first_ids.id = ji.id
ну и так далее. Я, правда, не могу проверить, но это должно сработать

[Тимофей]

Ой, ошибся, я ошибся. Вот так надо:
...
LEFT JOIN (
SELECT MIN(id) as id, inner.pluginid as pluginid
FROM jml_jquery_images inner
GROUP BY inner.pluginid
) ji_first_ids ON jp.pluginid = ji_first_ids.pluginid
...

Answer 2

[PedroGarciyaLopez]

попробуй так
SELECT DISTINCT имя(имена)_колонки FROM имя_таблицы

xml.nsu.ru/sql/sql_distinct.xml

Comments

[Валерий Чупурнов]

distinct срабатывает только полностью для строки, а она у меня всегда разная.

[PedroGarciyaLopez]

skoder: не совсем понял что ты имеешь в виду?

Answer 3

[Rsa97]

Всё верно возвращает, для каждой строки первой таблицы выбираются все подходящие строки второй таблицы или NULL, если они отсутствуют.

SELECT `jp`.*, `ji`.`image`
  FROM `jml_jquery_plugins` AS `jp`
  LEFT JOIN `jml_jquery_images` as `ji` 
    ON `ji`.`pluginid` = `jp`.`id`
  LEFT JOIN (SELECT MIN(`id`) AS `min`, `pluginid` FROM `jml_jquery_images` GROUP BY `pluginid`) AS `t`
    ON `t`.`pluginid` = `jp`.`id`
  WHERE `jp`.`del` != 1 
    AND `jp`.`lastupdate` > 0 
    AND (`t`.`min` = `ji`.`id` OR `ji`.`image` IS NULL)
  ORDER `jp`.`name` ASC
  LIMIT 20