SQl JOIN странно считает?

Question

[Кирилл Горелов]

Парни, привет.

Столкнулся с проблемой, JOIN странно считает количество записей в таблицах.
Задача: нужно посчитать в нескольких таблицах количество записей с нужным id.
Строю запрос, вроде считает, но именно вывод не правильно формирует.

SELECT t.id,t.master_theme, t.theme, COUNT(post.theme_id) as post_theme, COUNT(video.theme_id) as video_theme, COUNT(subscribe_theme.theme_id) as subscribe_theme 
FROM `theme` as t 
LEFT OUTER join post ON t.id = post.theme_id 
LEFT OUTER join video ON t.id = video.theme_id 
LEFT OUTER join subscribe_theme ON t.id = subscribe_theme.theme_id 
WHERE t.id in (88,14,91,23,79,13,21,24,89,17,65,35,97,82,37,38,85,64,56,90,60,99,16,76) 
GROUP BY t.id

Вывод:


Суть в том, что в строке id=35 subscribe_theme по факту имеет только одну запись, а выводит две. ТО есть как будто копирует значение из post_theme.

Еще возникает странность, когда в subscribe_theme реально число строк 2


ТО в общий вывод попадает уже 4:

Answer 1

[Rsa97]

JOIN - декартово умножение.
Одна строка из `theme`, к ней две из `post s`, ноль из `video` игнорируем (LEFT), одна из `subscribe`
Перемножаем, 1*2*1*1 = 2, что вы и получаете.
Если в `subscribe` будут две строки, получим 1*2*1*2 = 4

Comments

[Кирилл Горелов]

Класс, а как мне переделать??
Не подскажешь?
Хотя бы в какой сторону смотреть.

[Rsa97]

Кирилл Горелов, Чтобы соответствовало всем стандартам, примерно так:

SELECT `t`.`id`, `t`.`master_theme`, `t`.`theme`, IFNULL(`p`.`count`, 0) AS `posts`,
       IFNULL(`v`.`count`, 0) AS `videos`, IFNULL(`s`.`count`, 0) AS `subscribers`
  FROM `theme` AS `t`
  LEFT JOIN (
    SELECT `theme_id`, COUNT(*) AS `count`
      FROM `post`
      WHERE `theme_id` in (88,14,91,23,79,13,21,24,89,17,65,35,97,82,37,38,85,64,56,90,60,99,16,76)
      GROUP BY `theme_id`
  ) AS `p` ON `p`.`theme_id` = `t`.`id`
  ... Аналогично для других таблиц ...
  WHERE `t`.`id` in (88,14,91,23,79,13,21,24,89,17,65,35,97,82,37,38,85,64,56,90,60,99,16,76)

[Руслан .]

Кирилл Горелов, можно вот так попробовать:

SELECT t.id,
       t.master_theme,
       t.theme,
       COUNT(DISTINCT post.theme_id) as post_theme, 
       COUNT(DISTINCT video.theme_id) as video_theme, 
       COUNT(DISTINCT subscribe_theme.theme_id) as subscribe_theme

[Кирилл Горелов]

Rsa97, Очень выручил, спасибо)))

[Rsa97]

Руслан ., Здесь та же ошибка. По стандарту при группировке в выборку должны входить только агрегатные функции и/или поля, по которым идёт группировка. master_theme и theme не попадают ни туда, ни туда.
Такой запрос не будет работать в новых версиях MySQL с настройками по умолчанию.

[Руслан .]

Rsa97, Тут вы правы, я просто хотел показать, что вот такая конструкция:
COUNT(DISTINCT subscribe_theme.theme_id) as subscribe_theme
При подсчете суммы не будет учитывать дубли, т.е. подсчитает количество только уникальных идентификаторов.

Answer 2

[Андрей]

чтобы корректно работало, надо примерно так:

select
count(a.id) as all_a,
sum(ifnull(b.id, 0)) as all_b,
sum(ifnull(c.id, 0)) as all_c
from a left join b using(b_join) left join c using(c_join)
group by a.id