Struggling with MySQL query using joins

Question

I have 3 tables for storing information about books:

books
-----
id
title

authors
-------
id
name

books_to_authors
----------------
book_id
author_id

When a book's information is displayed, I then want to be able to select any other books by the same authors.

I have the current book id available from the first query, but I can't figure out where to start to achieve what I need as there can be multiple authors. Obviously with just one of them it would be simple, so I'm really struggling with this. Any help would be much appreciated!

Answer 1

I think this aught to do it. Just replace the ? with the book ID they are currently viewing and this will give you all the books by the same author.

SELECT b.*
FROM books b
INNER JOIN books_to_authors b2a ON b2a.book_id = b.id
WHERE b2a.author_id IN (
  SELECT author_id FROM books_to_authors WHERE book_id = ?
)

If you want to exclude the book they are currently viewing, you can change the query like this:

SELECT b.*
FROM books b
INNER JOIN books_to_authors b2a ON b2a.book_id = b.id
WHERE b2a.author_id IN (
  SELECT author_id FROM books_to_authors WHERE book_id = ?
)
AND b.id <> ?

Answer 2

$book_id = (your code for retrieving book_id);    

$db_query = "SELECT b.*
FROM books b
INNER JOIN books_to_authors bta ON bta.book_id = b.id
WHERE bta.author_id IN (
  SELECT author_id FROM books_to_authors WHERE book_id = ".$book_id."
)";

I presumed that you are using php. If I'm wrong, just use SQL query string, and ignore the rest...

Answer 3

You're looking for the query below. I see some solutions with subqueries and I'd highly recommend not using subqueries. They are slower than running 2 queries:

1. Having the book id you do SELECT author_id FROM books_to_authors WHERE book_id = '{$book_id}'

2. Get the author id and then run this:

   SELECT books.id, books.title, authors.name FROM books RIGHT JOIN books_to_authors ON books_to_authors.book_id = books.id) RIGHT JOIN authors ON (authors.id = books_to_authors.author_id) WHERE authors.id = '{$author_id}'