Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 6 down vote favorite

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!

share|improve this question

2 Answers

up vote 2 down vote accepted

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

share|improve this answer
1  
+1 for adding a fiddle example in there :) – webnoob Dec 11 '12 at 11:57
Thanks for your excellent answer. This works! – user1894374 Dec 11 '12 at 12:18
up vote 1 down vote

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'
share|improve this answer
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.