Get information from another table using intermediate table

Question

Here I have three tables authors, books_has_authors and books.

Books_has_author is an intermediate table which contains only the primary keys form other two tables. I am trying to write an sql query which will return me all the books name written by a particular author. I am new to relational database concepts as well as writing queries for relational databases. I know some join queries but I'm really confused how to use them for fetching information through an intermediate table. Any help will be appreciated ! :)

Answer 1

You only need to add an additional JOIN, like this:

SELECT b.book_id, b.book_name, a.author_id, a.author_name
FROM books b
JOIN books_has_author ba ON ba.books_book_id = b.book_id
JOIN author a ON ba.author_author_id = a.author_id
WHERE a.author_id = xxx

Answer 2

Try this:

  SELECT b.* FROM books b 
    INNER JOIN books_has_author bha 
    ON bha.books_book_id = b.book_id
    WHERE bha.author_author_id = "$$YOUR AUTHOR ID"