Can not determine what the WHERE clause should be

Question

I'm stuck with creating a MySQL query. Below is my database structure.

authors (author_id and author_name)

books (book_id and book_title)

books_authors is the link table (book_id and author_id)

Result of all books and authors:

I need to get all the books for certain author, but if a book has 2 authors the second one must be displayed also. For example the book "Good Omens" with book_id=2 has two authors. When I run the query I get the books for the author_id=1 but I can not include the second author - "Neil Gaiman" in the result. The query is:

SELECT * FROM books 
   LEFT JOIN books_authors 
       ON books.book_id=books_authors.book_id 
   LEFT JOIN authors 
       ON books_authors.author_id=authors.author_id 
WHERE books_authors.author_id=1

And below is the result:

Answer 1

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.