Форум MySQL

Долго мучаюсь над одним запросом - никак не могу сообразить.
Есть две таблицы

threads
id
name
description
author

messages
id
text
thread_id
author
add_date

В запросе нужно получить все записи из таблицы threads (тоесть список всех тем) и плюс к ним о каждой теме, колличество сообщений (COUNT(messages.id)), дату последнего сообщения (add_date) и автора последнего сообщения (с самым большим add_date).

В несколько запросов это без проблем, а вот как одним сделать... Подскажите пожалуйста.

примерно так.

SELECT threads.*, alls.cnt, alls.ldate, last.author AS lauthor FROM threads LEFT JOIN   (SELECT COUNT(id) AS cnt, MAX(add_date) AS ldate,  thread_id    FROM messages     GROUP BY thread_id)   AS alls ON threads.id = alls.thread_id   LEFT JOIN messages AS last ON alls.ldate = messages.add_date ORDER BY ldate DESC, cnt DESC

Спасибо, все отлично работает, только в одном месте не точность

SELECT threads.*, alls.cnt, alls.ldate, last.author AS lauthor  FROM threads  LEFT JOIN    (SELECT COUNT(id) AS cnt, MAX(add_date) AS ldate,  thread_id     FROM messages      GROUP BY thread_id)    AS alls ON threads.id = alls.thread_id    LEFT JOIN messages AS last ON alls.ldate = last.add_date  ORDER BY ldate DESC, cnt DESC

Все поставил но ничего не понял :) Учить ЭсКюЭл еще и учить...

Так тоже можно.
А что, оригинальный вариант выдал ошибку?
Я, честно сказать, запрос не проверял....

да, была ошибка

unknown table messages in on cause

Triaton, вы монстр такие запросы с ходу писать :)

>да, была ошибка
>unknown table messages in on cause
это потому что разные серверные платформы по-разному трактуют возможность применения псевдонимов для указания полей в отдельных частях sql-запроса.
MSSQL - по одному, MySQL по другому, ORACLE - по третьему....
мне аукается еще и то, что учился я по MSSQL, работать же пришлось на двух других платформах. Никогда не помнишь, как правильно :)

>
>Triaton, вы монстр такие запросы с ходу писать :)
На самом деле, чтобы их писать, нужно чувствовать очень немногое.
1. Что такое агрегатные функции
2. Что такое GROUP BY и как оно взаимодействует с ними
3. что такое JOIN ... ON и LEFT JOIN ON
4. что сформулированный селект может служить таблицей.
И всё пожалуй.

Правда и это позволяет писать довольно много достаточно эффективных запросов. По сравнению с простейшими.

Увы, тонкости SQL - стократ шире.
Увы потому что я ими не владею.
Пока не владею.

Никогда не понимал, что можно написать в 1000 страничных книах по SQL, которые встречаются в магазинах. Теперь понимаю :)

Кто может дать комментари к даному запросу (разложить всё по полочкам)?

Запрос, таблица результата которого, обозначена как alls

SELECT COUNT(id) AS cnt, MAX(add_date) AS ldate,  thread_id      FROM messages       GROUP BY thread_id

- этот запрос вопросов не вызывает?
Если вызывает, то какие?

В этом запросе незнаю что делает COUNT и MAX, а сама структура запроса понятна

>

>SELECT COUNT(id) AS cnt, MAX(add_date) AS ldate,  thread_id   >   FROM messages   >    GROUP BY thread_id >

Отлично.
COUNT(id) определяет количество строк, попадающих в группу с определенным thread_id
MAX(add_date) вычисляет максимальное значение поля add_date, встретившееся в таких строках,
результатом будет таблица из трех столбиков ( id_темы, число_сообщений_темы, дата_последнего сообщения)
Таблица будет обозначена как alls.

Пробовал сам решить сложившуюся задачу, но не могу проблема в GROUP BY
вот у меня есть такой запрос к БД

SELECT news.news_title  AS news_title, news.news_id AS news_id, news.news_comments_counter AS news_comments_counter FROM news, news_comments WHERE news.news_id = news_comments.news_comments_news_id  GROUP BY news.news_date ORDER BY news_comments.news_comments_date DESC limit 10";

Вся проблема заключается в том, что GROUP BY news.news_date группирует записи и при этом не верно определяет последнюю дату (мне нужно, чтоб вывод основывался на последней дате) если я уберу GROUP BY news.news_date то работает всё верно, но тогда выводятся повторения

Фух, не знаю понятно объяснил или нет!

Запрос семантически двусмысленнен. Сервер MySQL плюет на двусмысленность, (cheops, как я заметил - тоже :-) и выдает любую из строк в качестве результата. Но я - не сервер, и я такого запроса просто не понимаю.

Вы заказали группировку по news.news_date . Это значит, что весь набор строк будет разбит на группы по датам. От каждой группы нужно получить по одному затребованному в SELECT значению поля..
Но Вы просите поля, которые не входят в объявление группы (и не вычисляются агрегатно, как MAX например) - а значит могут быть разными в разных строках одной группы.

Из какой из строк брать значение? Я не понимаю смысла этого запроса.

Я немного ошибся, сори

SELECT news.news_title  AS news_title, news.news_id AS news_id, news.news_comments_counter AS news_comments_counter FROM news, news_comments  WHERE news.news_id = news_comments.news_comments_news_id   GROUP BY news.news_title ORDER BY news_comments.news_comments_date DESC limit 10";

Я хочу, чтоб он группировал их в порядке возрастания, например к новости, 1 было дано 20 комментариев, а к новости 30 было дано 2, причём последним комментом был коммент к новости 1, но в этом случаи он все равно выведет 30 новость

>Из какой из строк брать значение? Я не понимаю смысла этого запроса.
Просто надо вывести заголовок и id новости с последним комментарием

>>Из какой из строк брать значение? Я не понимаю смысла этого запроса.
>Просто надо вывести заголовок и id новости с последним комментарием

Это задача в общем виде.
Приведенный запрос её не решает. Почему - я сказал выше. Он неоднозначен.
Сервер не человек, чтобы догадываться о намерениях, задачу ему нужно ставить четко.

Тогда сделаем так.
Допустим есть такой запрос:

SELECT news.news_title  AS news_title, news.news_id AS news_id, news.news_comments_counter AS news_comments_counter FROM news, news_comments   WHERE news.news_id = news_comments.news_comments_news_id    ORDER BY news_comments.news_comments_date DESC limit 10";

Теперь стоит вопрос сгрупировать все одинаковые новости

в каком смысле - сгруппировать?

при выводе будет такое

1. как похудеть [10]
2. ЧПУ [2]
3. Как похудеть [10]
4. MSQ [1]
5. Кадеты [32]
6 Кадеты [32
7 Кадеты [32]
8 Кадеты [32]
9 Как похудеть [10]
10 Кадеты [32]

Тоесть тот скрипт выведет последних 10 комментариев.

Я хочу чтоб он вывел так:
1. как похудеть [10]
2. ЧПУ [2]
4. MSQ [1]
5. Кадеты [32]

Примерно так

news.news_comments_counter - это поле таблицы?

Да
news_comments_counter - количество комментариев

Тогда почему не добавить еще одно поле
news.news_comments_lasttime - время последнего коментария ?
тогда строить соединеие двух таблиц вообще не потребуется.

эта идея мне приходила в колову! Но просто уже сильно много комментов накопилось!

Какая разница?
Считать то всё равно запрос будет.

Я так понял это и всё!
Спасибо за помощь, буду думать как всётаки сделать!!!