8PbShrBc

1CREATE TABLE IF NOT EXISTS `authors` (
2  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
4  PRIMARY KEY (`id`)
5) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;
6
7CREATE TABLE IF NOT EXISTS `authors_books` (
8  `author_id` int(10) unsigned NOT NULL,
9  `book_id` int(10) unsigned NOT NULL,
10  UNIQUE KEY `unique_index` (`author_id`,`book_id`),
11  KEY `author_id` (`author_id`),
12  KEY `book_id` (`book_id`),
13  KEY `author_id_2` (`author_id`)
14) ENGINE=InnoDB DEFAULT CHARSET=latin1;
15
16CREATE TABLE IF NOT EXISTS `books` (
17  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
18  `title` mediumtext CHARACTER SET utf8 NOT NULL,
19  PRIMARY KEY (`id`)
20) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;
21
22ALTER TABLE `authors_books`
23  ADD CONSTRAINT `authors_books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE,
24  ADD CONSTRAINT `authors_books_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE;
25
26SELECT books.title as title, COUNT(authors.id) AS authors_count FROM `books`
27INNER JOIN authors_books ON (authors_books.book_id = books.id)
28INNER JOIN authors ON (authors_books.author_id = authors.id)
29GROUP BY books.id
30HAVING authors_count = 3