6

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!

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user1894374
  • 233
  • 3
  • 19

2 Answers2

2

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

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
1

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'
Muqito
  • 1,369
  • 3
  • 13
  • 27
  • 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)? – user1894374 Dec 11 '12 at 12:07