Форум пользователей MySQL

KnKill · 08.02.2011 18:16:32

Приветствую пользователей этого форума. Возник вопрос. Пусть есть 2 таблицы:

CREATE TABLE IF NOT EXISTS `quotes` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `quote` text,

  `author` text,

  `added` datetime DEFAULT NULL,

  `rating` int(10) DEFAULT NULL,

  `votes` int(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `comments` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `quote_id` int(10),

  `comment` text,

  `date` datetime DEFAULT NULL,

  `author` tinytext,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Необохдимо составить запрос, который возвращал все поля таблицы Quotes (ну просто

SELECT * FROM `quotes`

) и ещё одно поле, которые будет содержать количество элементов в таблице Comments, у когорых значение quote_id равно id из таблицы Quotes.

Например

Quotes:
id quote author added rating votes
1 t1 aut date 0 0
2 t2 aut date 0 0
3 t3 aut date 0 0

Comments:
id quote_id comment date author
1 2 bla d/t aut
2 2 bla d/t aut
3 3 bla d/t aut

Должно вернуть

id quote author added rating votes comments_count
1 t1 aut date 0 0 0
2 t2 aut date 0 0 2
3 t3 aut date 0 0 1

Заранее благодарю.

paulus · 09.02.2011 02:09:48

SELECT q.id, q.quote, ... COUNT(c.*)

FROM quotes q, comments c

WHERE c.quote_id = q.id

GROUP BY q.id, q.quote, ...               -- все, кроме COUNT

На самом деле, в MySQL можно написать просто GROUP BY q.id (и будет работать),
но это, строго говоря, нарушение логики SQL.

KnKill · 09.02.2011 10:54:48

К сожалению ничего не вышло:

Используя этот запрос

SELECT q.id, q.quote, COUNT(c.*) AS count

FROM quotes AS q, comments AS c

WHERE c.quote_id = q.id

GROUP BY q.id

Получаю такую ошибку:

Если заменить c.* на, например, c.id, то всё работает, но извлекаются только те записи которые содержат хотя бы один элемент с quote_id равны id из таблицы quotes.

В чём может быть проблема?

paulus · 09.02.2011 11:11:29

Да, со звездочкой погорячился

SELECT q.id, q.quote, COUNT(*) AS count
FROM quotes AS q LEFT JOIN comments AS c ON c.quote_id = q.id
GROUP BY q.id

Вот так будет выбирать даже записи без соответствий в комментариях.

KnKill · 13.02.2011 23:55:12

Спасибо огромное за Вашу помощь. Код рабочий, но возвращает единицу даже в случае, если у цитаты нет комментария. Но c.quote_id возвращается только в случае, если хотя бы одна цитата есть, поэтому я сделал так:

SELECT q.id, q.quote, q.added, q.rating,

                                 IF(c.quote_id is null, 0, COUNT(*)) as count

                                 FROM `quotes` AS q

                                 LEFT JOIN `comments` AS c ON c.quote_id = q.id

                                 GROUP BY q.`id`

                                 ORDER BY q.`id` DESC

Не знаю, верно или нет, но работает. Ещё раз спасибо за помощь.

У меня появился ещё один вопрос. Я добавил таблицу favorites, с полями id, quote_id, user_id. Можно ли к уже этому запросу присоединить количество записей от пользователя к каждой записи (каждый отдельный пользователь может добавить только один раз, поэтому число больше одного не будет в любом случае).

Точно не знаю как описать что нужно сделать, напишу саму задачу, может это немного прояснит суть проблемы.

Нужно чтобы в результате запроса было ещё одно поле по которому можно было бы определить, добавил ли пользователь цитату к себе в избранные или нет.
ID пользователя передаётся php скриптом напрямую в запрос.

Надеюсь, объяснил суть вопроса более-менее понятно

Сам попробовал множество способов, прочитал много информации по этой теме, но так и не смог написать запрос.
Надеюсь на Вашу помощь. Заранее благодарю.

Отредактированно KnKill (13.02.2011 23:59:20)

paulus · 14.02.2011 01:25:27

Тьфу ты, разумеется, возвращает единицу В левой таблице всегда же есть
строка, даже если в правой ничего нет. Надо считать явно — количество строк
в правой таблице. Давайте попробуем еще одну итерацию — COUNT(c.quote_id)
вместо COUNT(*)

Добавить можно, точно так же — левым объединением. И считать точно
так же — количество строк, которые есть в «правой» таблице.

KnKill · 14.02.2011 01:39:23

Спасибо Вам большое. Всё отлично работает!

KnKill · 18.02.2011 00:41:45

Появилась ещё одна проблемка. С JOIN'ами я более менее разобрался, но следующая задача поставила меня в тупик.
Есть запрос

SELECT q.`id`,

q.`quote`,

q.`user_id`,

q.`added`,

u.login,

IF(COUNT(f.`user_id`) = 0, 0, 1) AS fav, 

COUNT(c.`quote_id`) AS comments

FROM `quotes` AS q

LEFT JOIN `favorites` AS f ON f.`quote_id` = q.`id` AND f.`user_id` = 3

LEFT JOIN `comments` AS c ON c.`quote_id` = q.`id`

LEFT JOIN `users` AS u ON u.`id` = q.`us

который нормально работает и всё возвращает.

Но стоит добавить ещё один LEFT JOIN, и значения в результате становятся совершенно неверными.

Измёнённый запрос выглядит так:

SELECT  q.`id`,

q.`quote`,

q.`user_id`,

q.`added`,

u.login,

IF(COUNT(f.`user_id`) = 0, 0, 1) AS fav, 

COUNT(c.`quote_id`) AS comments,

SUM(v.`added`) AS votes_sum

FROM `quotes` AS q

LEFT JOIN `favorites` AS f ON f.`quote_id` = q.`id` AND f.`user_id` = 3

LEFT JOIN `comments` AS c ON c.`quote_id` = q.`id`

LEFT JOIN `users` AS u ON u.`id` = q.`user_id`

LEFT JOIN `votes` AS v ON v.`quote_id` = q.id

GROUP BY  q.`id`

После выполнения этого запроса, в результате стоит неверная сумма из таблицы added. Причём в столбце comments результат тоже становится неверным.

EXPLAIN запроса:
"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"q";"ALL";NULL;NULL;NULL;NULL;"7";"Using temporary; Using filesort"
"1";"SIMPLE";"f";"ALL";NULL;NULL;NULL;NULL;"19";""
"1";"SIMPLE";"c";"ALL";NULL;NULL;NULL;NULL;"6";""
"1";"SIMPLE";"u";"eq_ref";"PRIMARY";"PRIMARY";"4";"cool_quotez.q.user_id";"1";""
"1";"SIMPLE";"v";"ALL";NULL;NULL;NULL;NULL;"5";""

Получилось составить запрос с помощью вложенного SELECT запроса:

SELECT q.`id`,

q.`quote`,

q.`user_id`,

q.`added`,

u.login, IF(COUNT(f.`user_id`) = 0, 0, 1) AS fav, COUNT(c.`quote_id`) AS comments,

(

SELECT SUM(`added`)

FROM votes

WHERE `quote_id` = q.id) AS vote

FROM `quotes` AS q

LEFT JOIN `favorites` AS f ON f.`quote_id` = q.`id` AND f.`user_id` = 3

LEFT JOIN `comments` AS c ON c.`quote_id` = q.`id`

LEFT JOIN `users` AS u ON u.`id` = q.`user_id`

GROUP BY q.`id`

но как я понимаю это не совсем правильно

Отредактированно KnKill (19.02.2011 18:49:12)

paulus · 21.02.2011 12:30:39

COUNT() возвращает количество строк, которое он находит в объединенных
таблицах. Т.е. если у Вас простой запрос
SELECT COUNT(*) FROM A JOIN B
и в таблице A 3 строки, а в B — 2, то COUNT() вернет 6 (очевидно). Когда
Вы пишете условие ON (или WHERE) — вы ограничиваете количество строк
B. Если Вы ограничиваете до одной строки — COUNT выводит «правильное»
количество строк из А. Если ограничение у Вас вывело более одной строки —
COUNT вернул строк больше.

Решение — не добавлять такие штуки в этот запрос

Если совсем хочется, и жить без этого не можете — сделайте подзапросом.

Форум пользователей MySQL

#1 08.02.2011 18:16:32

Помогите составить запрос

#2 09.02.2011 02:09:48

Re: Помогите составить запрос

#3 09.02.2011 10:54:48

Re: Помогите составить запрос

#4 09.02.2011 11:11:29

Re: Помогите составить запрос

#5 13.02.2011 23:55:12

Re: Помогите составить запрос

#6 14.02.2011 01:25:27

Re: Помогите составить запрос

#7 14.02.2011 01:39:23

Re: Помогите составить запрос

#8 18.02.2011 00:41:45

Re: Помогите составить запрос

#9 21.02.2011 12:30:39

Re: Помогите составить запрос

Board footer