As you choose in the table only the books that have exactly 4 of the author?

advertisements

Table Author:

id | name_author
---------
1 | david
2 | kate
3 | tom
4 | mark

Table books:

id | name_book
------------
1 | book1
2 | book2
3 | book3

table relationships authors and books

id_book | id_author
-------------------
1 | 2
1 | 3
1 | 4
2 | 2
1 | 1
3 | 4

As a result, I has to get the book "Book1" because it has 4 authors (david, kate, tom, mark).

How can I write a query to mysql?


SELECT
    b.name_book, GROUP_CONCAT(a.name_author) authors
FROM relationships r
JOIN books b ON r.id_book = b.id
JOIN author a ON r.id_author = a.id
GROUP BY r.id_book
HAVING COUNT(r.id_book) = 4
;