MySQL SELECT multiple rows based on specific field value and number of rows

group-by, having-clause, mysql, select, sql

Solution

Try this:

SELECT a.book_id
FROM authorbook a
INNER JOIN book b
ON a.book_id = b.book_id
WHERE b.book_title='Atitle'
  AND FIND_IN_SET (a.aut_id,'1,2')
GROUP BY a.book_id
HAVING COUNT(DISTINCT a.aut_id) = 2
   AND COUNT(DISTINCT a.aut_id) = (SELECT COUNT(DISTINCT a2.aut_id)
                                   FROM authorbook a2 
                                   WHERE a2.book_id = a.book_id);

SQL Fiddle Demo

Problem

I have three tables: ``` author (columns: aut_id, aut_name) book (columns: book_id, book_title) authorbook (linking table, columns: aut_id, book_id) ``` Each author can be associated with one or more books. Each book can be associated with one or more authors. I would like to select a book by the name(s) and the exact number of its authors. Table structure: ``` author aut_id | aut_name 1 Aname 2 Bname 3 Cname book book_id | book_title (the titles are identical on purpose) 1 Atitle 2 Atitle 3 Atitle authorbook aut_id | book_id 1 1 1 2 2 2 1 3 2 3 3 3 ``` Here is my code (I left out the author table for better clarification): ``` SELECT authorbook.book_id FROM authorbook INNER JOIN book ON authorbook.book_id = book.book_id WHERE book_title='Atitle' AND FIND_IN_SET (authorbook.aut_id,'1,2') GROUP BY authorbook.book_id HAVING (COUNT(authorbook.aut_id)=2) ``` Problem: This code not only returns the desired `authorbook.book_id(2)` with TWO `authorbook.aut_ids (1,2)` but also the `authorbook.book_id(3)` with THREE `authorbook.aut_ids (1,2,3)`. Question: How can I `SELECT` a book associated with exactly the authors in the `FIND_IN_SET` clause (and no additional authors)? Thanks a lot for your help!

Original source