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

Question

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!

Sojourn

Answer 1

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

Gray

Answer 2

This should work

SELECT COUNT(aut_id) AS authors, book_id FROM (SELECT authorbook.*
FROM authorbook 
INNER JOIN book
ON authorbook.book_id = book.book_id
WHERE book_title='Atitle') AS t1 GROUP BY book_id HAVING authors='2'

Quincy

Thanks a lot! This works. However, when I add another book (book_id = 4, aut_ids = 1,3) this query returns the new book as well (So I get two results - book_id 2 AND 4 - instead of the one (book_id 2) desired). Is there a way to get only the one result where the number of authors is 2 AND the two authors are the ones specified(1,2)? – Sojourn