Find value with many-to-many relationship table

Question

There is 3 tables.

Book. ID / Title

Author. ID / Name

Book_Author. Book_ID / Author_ID

The goal is to get all titles of books written by specific Author. We know the name of Author. I've tried an approach with join but it takes two steps to reach result anyhow I try.

for example:

select book_author.book_id, book_author.author_id  
from  book_author 
inner join author on book_author.author_id = author.id 
where name = 'author1';

select book.title 
from  book 
inner join author_to_book on book.id = book_author.book_id 
where author_id = 3;

Have tried to merge the methods but with no success so far. Any suggestions?

Does this answer your question? [MySQL join with where clause](https://stackoverflow.com/questions/1219909/mysql-join-with-where-clause) — pringi, Aug 05 '21 at 11:27

score 0 · Answer 1 · edited Aug 04 '21 at 19:31

0

Try using SubQueries:

SELECT b.ID, b.Title 
FROM Book AS b 
WHERE b.Id = (SELECT ID FROM Author AS a WHERE a.Name = 'Author Name Here');

edited Aug 04 '21 at 19:31

Barmar

741,623
53
500
612

answered Aug 04 '21 at 19:30

Patrick Son

1

This won't work, it's missing the `book_author` table that links them. – Barmar Aug 04 '21 at 19:31
`b.id` is a book ID, not the author ID. – Barmar Aug 04 '21 at 19:32

score 0 · Answer 2 · answered Aug 04 '21 at 19:30

0

Join all 3 tables

SELECT b.title
FROM book AS b
JOIN book_author AS ba ON b.id = ba.book_id
JOIN author AS a ON a.id = ba.author_id
WHERE a.name = 'author1'

answered Aug 04 '21 at 19:30

Barmar

741,623
53
500
612

Find value with many-to-many relationship table

2 Answers2