Can not determine what the WHERE clause should be

You need to change the WHERE clause to execute a subselect like this:

SELECT b.*, a.*
FROM books b
LEFT JOIN books_authors ba ON ba.book_id  = b.book_id
LEFT JOIN authors       a  ON a.author_id = ba.author_id
WHERE b.book_id IN (
  SELECT book_id
  FROM books_authors
  WHERE author_id=1)

The problem with your query is that the WHERE clause is not only filtering the books you are getting in the result set, but also the book-author associations.

With this subquery you first use the author id to filter books, and then you use those book ids to fetch all the associated authors.

As an aside, I do think that the suggestion to substitute the OUTER JOINs with INNER JOINs in this specific case should apply. The first LEFT OUTER JOIN on books_authors is certainly useless because the WHERE clause guarantees that at least one row exists in that table for each selected book_id. The second LEFT OUTER JOIN is probably useless as I expect the author_id to be primary key of the authors table, and I expect the books_authors table to have a foreign key and a NOT NULL constraint on author_id... which all means you should not have a books_authors row that does not reference a specific authors row.

If this is true and confirmed, then the query should be:

SELECT b.*, a.*
FROM books b
JOIN books_authors ba ON ba.book_id  = b.book_id
JOIN authors       a  ON a.author_id = ba.author_id
WHERE b.book_id IN (
  SELECT book_id
  FROM books_authors
  WHERE author_id=1)

Notice that INNER JOINs may very well be more efficient than OUTER JOINs in most cases (they give the engine more choice on how to execute the stament and fetch the result). So you should avoid OUTER JOINs if not strictly necessary.

I added aliases and removed the redundant columns from the result set.


You don't need a subquery for this:

SELECT *
FROM book_authors ba
   JOIN books b
     ON b.book_id = ba.book_id
   JOIN book_authors ba2
     ON ba2.book_id = b.book_id
   JOIN authors a
     ON a.author_id = ba2.author_id
WHERE ba.author_id = 1