I have the following tables:
table name -----| table fields
books -------------| book_id, book_title
authors ---------- | author_id, author_name
books_authors - | author_id, book_id
users ------------- | user_id, user_name
comments -------- | comment_id, comment
comments_users | comment_id, user_id, book_id, date
I need to show the book title, its authors, and all the comments posted for this book (I need to show user name, date, comment). I make do it with two different sql queries, but I don't know how to do it with just 1 query. Here are the two queries:
SELECT * FROM books
INNER JOIN books_authors
ON books.book_id=books_authors.book_id
INNER JOIN authors
ON books_authors.author_id=authors.author_id
WHERE books.book_id=7
this one returns the name and authors of book with id=7.
SELECT `user_name`, `comment`, `date`
FROM comments_users
INNER JOIN comments ON comments_users.comment_id = comments.comment_id
INNER JOIN users ON comments_users.user_id = users.user_id
WHERE comments_users.book_id=7
this one returns the user name, comment, and the date the comment was posted. Can anyone explain me how can I join them into one query? Thanks in advance
This arranged query should suffice, it will display results as you want :
SELECT books.book_title, authors.author_name, comments.comment FROM books
INNER JOIN books_authors
ON books.book_id=books_authors.book_id
INNER JOIN authors
ON books_authors.author_id=authors.author_id
LEFT JOIN comments_users ON comments_users.book_id = books.book_id
INNER JOIN comments ON comments_users.comment_id = comments.comment_id
INNER JOIN users ON comments_users.user_id = users.user_id
WHERE books.book_id=7
Can't you just JOIN all the tables from the two queries?
SELECT `books.book_id`, `books.book_title`, `comments_users.user_name`,
`comments_users.comment`, `comments_users.date`
FROM books
INNER JOIN books_authors ON books.book_id=books_authors.book_id
INNER JOIN authors ON books_authors.author_id=authors.author_id
INNER JOIN comments_users ON comments_users.book_id = books.book_id
INNER JOIN comments ON comments_users.comment_id = comments.comment_id
INNER JOIN users ON comments_users.user_id = users.user_id
WHERE books.book_id=7
Hope this is what you're trying to achieve.