Geek Answers Handbook

MySQL SELECT multiple rows based on specific field value and row count

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 may be associated with one or more authors.
I would like to choose a book by 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 missed 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 authorbook.book_id(3) with THREE authorbook.aut_ids (1,2,3) .

Question: How can I SELECT to create a book specifically associated with authors in the FIND_IN_SET (and without additional authors)? Many thanks for your help!

+6
source share
2 answers

Try the following:

 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

+2
source

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'
+1
source

More articles:


All Articles