CODEWITHSUNDEEP
mysqlsql
warini
warini

Reputation: 13

As select from table only those books which have exactly 4 of the author?

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?

Upvotes: 1

Views: 49

Answers (4)

cn0047
cn0047

Reputation: 17051

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
;

Upvotes: 0

cn0047
cn0047

Reputation: 17051

Maybe it:

SELECT b.name_book, a.name_author
FROM relationships r
JOIN books b ON r.id_book = b.id
JOIN author a ON r.id_author = a.id
WHERE b.name_book = 'Book1'
;

Result:

+-----------+-------------+
| name_book | name_author |
+-----------+-------------+
| book1     | kate        |
| book1     | tom         |
| book1     | mark        |
| book1     | david       |
+-----------+-------------+
4 rows in set (0.00 sec)

Or:

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
WHERE b.name_book = 'Book1'
;

Result:

+-----------+---------------------+
| name_book | authors             |
+-----------+---------------------+
| book1     | kate,tom,mark,david |
+-----------+---------------------+
1 row in set (0.00 sec)

Upvotes: 0

Alex
Alex

Reputation: 17289

As simple as:

SELECT COUNT(DISTINCT(id_author)) as authors_num,id_book
FROM table1
GROUP BY id_book
HAVING authors_num=4

and if you need book title:

SELECT COUNT(DISTINCT(table1.id_author)) as authors_num,
  table1.id_book, 
  books.name_book
FROM table1
LEFT JOIN books
ON books.id = table1.book_id
GROUP BY id_book
HAVING authors_num=4

Upvotes: 0

A.D.
A.D.

Reputation: 1180

you could write something like that (not tested)

select name_book
from books as b
, link_book_author as l
where b.id = l.id_book
group by name_book
having count(id_author) = 4

Upvotes: 1

Related Questions