Здравствуйте.
Подскажите, пожалуйста, как можно написать запрос.
Есть три таблицы:
CREATE TABLE books (
id int(11) AUTO_INCREMENT,
name varchar(120) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE authors (
id int(11) AUTO_INCREMENT,
name varchar(120) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE books_authors (
id int(11) AUTO_INCREMENT,
book_id int(11) NOT NULL,
author_id int(11) NOT NULL,
PRIMARY KEY (id)
);
Таблица books_authors - естественно, для связи между авторами и книгами. У одной книги может быть несколько авторов.
Стоит задача вывести название книги (в идеале - книг) с самым большим количеством авторов.
Написал следующий запрос:
SELECT DISTINCT `book_id` AS `idbook`, ( SELECT COUNT(`author_id`) FROM `books_authors` WHERE `book_id` = `idbook` ) AS `counting` FROM `books_authors`
В результирующей таблице выводятся id книг и кол-во авторов для каждой из них.
Однако вывести id книг(и) с максимальным кол-вом авторов не получается.
SELECT * FROM (
SELECT DISTINCT `book_id` AS `idbook`, ( SELECT COUNT(`author_id`) FROM `books_authors` WHERE `book_id` = `idbook` ) AS `counting` FROM `books_authors`
) `maxcount` HAVING `counting` = MAX(`counting`)
Подобный запрос, конечно, не работает.
Работает такое "масло масленное":
SELECT `idbook` FROM (
SELECT DISTINCT `book_id` AS `idbook`, ( SELECT COUNT(`author_id`) FROM `books_authors` WHERE `book_id` = `idbook` ) AS `counting` FROM `books_authors`
) `maxcount` HAVING `counting` =
(SELECT MAX(`counting`) FROM ( SELECT DISTINCT `book_id` AS `idbook`, ( SELECT COUNT(`author_id`) FROM `books_authors` WHERE `book_id` = `idbook` ) AS `counting` FROM `books_authors` ) `topcount` )
Но нужно ещё вывести названия книг и лишний запрос верхнего уровня вида SELECT `name` FROM `books` WHERE `id` IN ( ... ) не срабатывает.
Прошу подсказать, как можно реализовать данную выборку.
Заранее благодарю.
P.S. для заполнения таблиц:
/* книги */
INSERT INTO books(id, name) VALUES (1, 'Modern PHP: New Features and Good Practices');
INSERT INTO books(id, name) VALUES (2, 'Programming PHP');
INSERT INTO books(id, name) VALUES (3, 'Effective Python: 59 Specific Ways to Write Better Python');
/* авторы */
INSERT INTO authors(id, name) VALUES (1, 'Josh Lockhart');
INSERT INTO authors(id, name) VALUES (2, 'Kevin Tatroe');
INSERT INTO authors(id, name) VALUES (3, 'Peter MacIntyre');
INSERT INTO authors(id, name) VALUES (4, 'Rasmus Lerdorf');
INSERT INTO authors(id, name) VALUES (5, 'Brett Slatkin');
/* Связи между авторами и книгами */
INSERT INTO books_authors(book_id, author_id) VALUES(1, 1);
INSERT INTO books_authors(book_id, author_id) VALUES(2, 2);
INSERT INTO books_authors(book_id, author_id) VALUES(2, 3);
INSERT INTO books_authors(book_id, author_id) VALUES(2, 4);
INSERT INTO books_authors(book_id, author_id) VALUES(3, 5);
