CODEWITHSUNDEEP
mysqlsql
m4rv
m4rv

Reputation: 23

MySQL select unique values from a relationship table

I am currently working on a MYSQL project which has a few fairly standard many to many relationship.

I've shown a greatly simplified relationship table to aid my example.

table: book_authors
ID    BOOKS   AUTHORS
1     1       4
2     1       5
3     4       4
4     4       5
5     2       6
6     2       1
7     2       5
8     3       6
9     3       5
10    3       1
12    5       2
13    6       2
14    7       5

What I'm looking to achieve is to be able to select all books which have specified authors, and only get the books which match all of the supplied authors. The number of authors requested each time is also variable.

So if i'm looking for all books written by author 4 and author 5, I'll get result of books 1 and 4 only. If im looking for books written by author 5 only, i'll get book 7 only.

Upvotes: 2

Views: 670

Answers (2)

Phil
Phil

Reputation: 497

If it's a one off you could do: not very repeatable though.

select distinct
  ba1.books
from
  book_authors ba1
join
  book_authors ba2
    on ba1.id<>ba2.id and ba1.books=ba2.books
where
  ba1.authors=5
  and ba2.authors=4

edit: forgot part of the join

Upvotes: 0

ruakh
ruakh

Reputation: 183504

I don't think there's a very smooth way to do this, but if performance isn't a big concern, you can exploit MySQL's GROUP_CONCAT function, and write this:

SELECT books
  FROM book_authors
 WHERE books IN
        ( SELECT books
            FROM book_authors
           WHERE authors = 4
        )
 GROUP
    BY books
HAVING GROUP_CONCAT(authors ORDER BY authors) = '4,5'
;

(The WHERE clause isn't even needed for correct results, but it makes the query less gratuitously expensive.)

Upvotes: 1

Related Questions