Форум MySQL

Имеем три таблицы: книги, люди и связующая - авторы.
Очень логично. У одной книги может быть несколько авторов, а один человек может быть автором нескольких книг.

Books [id, title]
Authors [idbook, idperson]
Persons [id, name]

Мне нужна такая результирующая таблица, чтобы в ней были следующие столбцы:
название книги, имя автора(-ов)

Использую вот такой код:

SELECT books.title, persons.name  FROM books  LEFT JOIN authors LEFT JOIN persons ON authors.idperson=persons.id ON books.id=authors.idbook

Однако, если у книги больше одного автора, то запись этой книги повторяется столько раз, сколько у нее авторов.
Можно ли сделать так, чтобы запись не дублировалась, а авторы перечислялись через запятую?

Нет этого невозможно добиться - у вас же каждой книге соответствует разное количество столбцов, а результирующая таблица имеет фиксированное количество столбцов. Тут пожалуй без дополнительных запросов не обойтись.

Подскажите, пожалуйста, что и куда здесь нужно добавить, чтобы получить то, что мне нужно

<? $query="SELECT books.title AS title, persons.name AS name         FROM books          LEFT JOIN authors         LEFT JOIN persons         ON authors.idperson=persons.id         ON books.id=authors.idbook"; $result=mysql_query($query); if ($result)  {     $numrows=mysql_num_rows($result);     for($i=1;$i<=$numrows;$i++)      {         $el = mysql_fetch_array($result, MYSQL_ASSOC);         echo $el['title']." -- ".$el['title'].;     } } ?>

Можно поступить следующим образом

<?    $query="SELECT * FROM books"   $result = mysql_query($query);    if ($result)     {      if(mysql_num_rows($result))     {       while($el = mysql_fetch_array($result, MYSQL_ASSOC))       {         // Извлекаем авторов         $query = "SELECT persons.name AS name                   FROM authors JOIN persons ON authors.idperson=persons.id                    WHERE authors.idbook = $el[id]                   GROUP BY authors.idbook                   ORDER BY name";         $ath = mysql_query($query);         if($ath) exit("Ошибка извлечения списка авторов");         $author = array();         if(mysql_num_rows($ath))         {           while($result = mysql_fetch_array($ath, MYSQL_ASSOC))           {             $author[] = $result['name'];           }         }         // Выводим запись о книге         echo $el['title'];          if(!empty($author)) echo " -- ".implode(", ", $author)         echo "<br>";       }     }   }  ?>

SELECT books.title, GROUP_CONCAT(persons.name ORDER BY persons.name SEPARATOR ', ' ) AS author_names   FROM books    LEFT JOIN authors ON books.id=authors.idbook   LEFT JOIN persons ON authors.idperson=persons.id

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
Заметьте также, как правильно пишется операция LEFT JOIN .

За наводку на такую полезную функцию, как GROUP_CONCAT большое спасибо!
Но вот пример почему-то не сработал.

У вас дамп большой? Если не сложно, прикрепите его к сообщению, чтобы можно было воспроизвести ситуацию?

А как сделать дамп?

Вам доступен phpMyAdmin? Если да, то всех проще его сделать в меню "Експорт", если phpMyAdmin не доступен, то можно воспользоваться утилитой mysqldump

mysqldump -u root base > base.sql

root - имя пользователя
base - название базы данных
base.sql - имя файла, куда сохраняется дамп

Спасибо за подсказку. Сделано.

Добавьте группировку по полю books.title

SELECT books.title, GROUP_CONCAT(persons.name ORDER BY persons.name SEPARATOR ', ' ) AS author_names  FROM books LEFT JOIN       authors ON books.id=authors.idbook LEFT JOIN      persons ON authors.idperson=persons.id GROUP BY books.title

Спасибо! Получилось!
А почему так? Как одна группировка могла повлиять на выполнение запроса?

Функция GROUP_CONCAT() - агрегатная. То есть при корректно построенном запросе она обрабатывает все строки, попадающие в выборку (если GROUP BY не указан) или в каждую из групп (если GROUP BY указан) . Но в первом случае в выражении SELECT вообще не должны участовать ссылки на выборку кроме как внутри агрегатных функций. Поскольку запрашивался books.title вне агрегата и без GROUP BY по нему - запрос был отклонен.

Дело в том, что функция GROUP_CONCAT() реагирует на группы, когда вы используете группировку GROUP BY books.title - конструкция создает количество групп соответствующее количеству уникальных значений books.title. Если GROUP BY не используется, считается, что группа одна - все возвращаемые записи - поэтому выводится только одна книга и все авторы, которые только нашлись.

>Если GROUP BY не используется, считается, что группа одна - все возвращаемые записи - поэтому выводится только одна книга и все авторы, которые только нашлись.

Утверждение ложное.

Ответом на этот запрос будет вот такая диагностика.
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Даже MySQL-серверу нужен хотя бы один группирующий параметр.
Он (если не включен режим ONLY_FULL_GROUP_BY) плюнет на оставшиеся (кроме перечисленных в GROUP BY) , выраждения в SELECT . Но для этого нужно хоть что-то перечислить.
Если ONLY_FULL_GROUP_BY включен - любой GROUP-неоднозначный запрос будет отклонен.

Само собой, это не специфика GROUP_CONCAT, другие агрегатные функции (MAX, AVG, COUNT и пр.) ведут себя аналогично.

Если не сложно гляньте вложение и сообщите версию вашего MySQL-сервера (у меня 5.1.37).

>Утверждение ложное.
Слишком сильное и неоднозначное утверждение, его можно читать так, что вы отрицаете наличие группы, совпадающей с таблицей в запросах вида

SELECT COUNT(id) FROM tbl;

Т.е. такие запросы должны вызывать ошибку, что разумеется не так. Если GROUP BY нет - группой считается вся таблица.

>Если не сложно гляньте вложение

глянул. Удивился.

>и сообщите версию вашего MySQL-сервера (у меня 5.1.37).
5.0.45


>Слишком сильное и неоднозначное утверждение,
Да, наверное. Никогда нельзя делать однозначных утверждений, рассматривая поведение серверов на неоднозначных запросах. :)

>его можно читать так, что вы отрицаете наличие группы, совпадающей с таблицей в запросах вида
SELECT COUNT(id) FROM tbl;

А вот это - дудки.

Я протестую лишь против чего-то вроде
SELECT title, COUNT(id) FROM tbl;
и
SELECT title, version, COUNT(id) FROM tbl GROUP BY title;
Да и то, вобщем-то, лишь в том случае, если title не определяет version однозначно (т.е. когда все висящие поля (вроде version в этом примере) не меняют своих значений на всем диапазоне строк группы.)


В запросе SELECT COUNT(id) FROM tbl; нет ни одной ссылки на таблицу не из агрегатной функции, которая не была бы перечислена в GROUP BY, просто потому, что "нет ни одной ссылки на таблицу не из агрегатной функции" вообще.


PS. Естественно, в первом примере дописать GROUP BY я просто забыл...
само собой разумеющиеся вещи иногда выскакивают из головы.


PS2. Если рассмотреть пример из Вышего скриншота, он лишь подтверждает мысль.
Почему именно "Вычислительные машины и труднорешаемые задачи", а не какая-то другая книга?
Я даже не могу сказать, что потому, что первой попалась серверу.
Может не первой, а наоборот - последней.