Как узнать количество записей в дочерней таблице (MySQL)

Задача

Например у нас есть 2 таблицы:

  • news — Новости
  • comments — Комментарии

Необходимо вывести список новостей и количество комментариев к каждой новости.

Данные

Таблицы следующего вида

Новости (news)

  • id — айди
  • header — заголовок
  • text — текст

Создаем таблицу

CREATE TABLE `news` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL DEFAULT '0000-00-00', `text` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Заполняем данные

INSERT INTO `news` (`id`,`date`,`text`) VALUES (1,'2010-12-16','Опубликована первая статья'), (2,'2010-12-25','Опубликована десятая статья'), (3,'2011-01-04','Опубликована пятнадцатая статья'), (4,'2011-01-08','Исходный код языков программирования стал отображаться красивее. Еще пример'), (5,'2011-01-08','Открыта страница лица expange');

Комментарии (comments)

  • id — айди
  • id_news — айди-новости
  • username — имя комментатора
  • header — заголовок
  • text — текст

Создаем таблицу

CREATE TABLE `comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_news` int(10) unsigned NOT NULL DEFAULT '0', `username` varchar(20) NOT NULL DEFAULT '', `date` date NOT NULL DEFAULT '0000-00-00', `text` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_comments2news` (`id_news`), CONSTRAINT `FK_comments2news` FOREIGN KEY (`id_news`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Заполняем данные

INSERT INTO `comments` (`id`,`id_news`,`username`,`date`,`text`) VALUES (1,1,'Артем','2010-12-16','Круто!'), (2,1,'Вася','2010-12-16','Мои поздравления'), (3,1,'Коля','2010-12-17','Отлично! Так держать!'), (4,3,'Вася','2011-01-04','Поздравляю. Продолжаем в том же духе.'), (5,3,'Коля','2011-01-05','Проект развивается - это радует.'), (6,4,'Артем','2011-01-08','Отлично получилось. Здорово смотрится.'), (7,5,'Вася','2011-01-08','Прикольно.'), (8,4,'Вася','2011-01-09','Неплохо вышло, прямо как в редакторе.'), (9,1,'Ваня','2011-01-10','Супер. Отлично получилось.'), (10,3,'Артем','2011-01-12','Круто!'), (11,4,'Коля','2011-01-15','Да! Ништяк получилось :)'), (12,5,'Артем','2011-02-08','=)'), (13,5,'Коля','2011-02-13','Весело)))'), (14,5,'Ваня','2011-03-10','Первые лица :)');

Запрос

Придется использовать конструкцию LEFT JOIN, т.к. обычным включением таблицы comments в запрос не обойтись, т.к. тогда, в результат запроса войдут новости у которых один и более комментариев.

SELECT n.`id`, n.`date`, n.`text`, COUNT(c.`id`) FROM news n LEFT JOIN comments c ON (c.`id_news`=n.`id`) GROUP BY n.`id`;

Результат

iddatetextCOUNT(c.`id`)
12010-12-16Опубликована первая статья4
22010-12-25Опубликована десятая статья0
32011-01-04Опубликована пятнадцатая статья3
42011-01-08Исходный код языков программирования стал отображаться красивее. Еще пример3
52011-01-08Открыта страница лица expange4

Категории

СУБДMySQL

Комментарии