Задача
Например у нас есть 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`;
Результат
| id | date | text | COUNT(c.`id`) |
|---|---|---|---|
| 1 | 2010-12-16 | Опубликована первая статья | 4 |
| 2 | 2010-12-25 | Опубликована десятая статья | 0 |
| 3 | 2011-01-04 | Опубликована пятнадцатая статья | 3 |
| 4 | 2011-01-08 | Исходный код языков программирования стал отображаться красивее. Еще пример | 3 |
| 5 | 2011-01-08 | Открыта страница лица expange | 4 |