MySQL如何使用来自不同表的一个查询获得两个结果

Question

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

Answer 1

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