mysql - 三个表 INNER JOIN 和 WHERE 子句

Question

我有四张 table 1. tbl_threads2. tbl_comments3. tbl_votes4.tbl_users

假设当前登录的user_id =3thread_id = 10

现在我必须检索以下数据

All the fields from tbl_comments where tbl_comments.thread_id =10
All the fields from tbl_users based on the common key tbl_users.user_id = tbl_comments.user_id
All the fields from tbl_votes Where user_id =3 And tbl_votes.comment_id =tbl_comments.comment_id 

如何通过一个查询执行所有这些功能？

我试过下面的查询，但它给了我错误的结果

SELECT tbl_comments.*
     , tbl_users.*
     , tbl_votes.* 
FROM tbl_comments 
INNER JOIN tbl_users 
on tbl_comments.user_id = tbl_users.user_id 
WHERE thread_id= 10
INNER JOIN tbl_votes 
on tbl_votes.comment_id = tbl_comments.comment_id 
WHERE tbl_votes.user_id= 3

Answer 1

假设 thread_id 列在 tbl_comments 表中，将第一个 where 更改为 and:

SELECT tbl_comments.*
     , tbl_users.*
     , tbl_votes.* 
FROM tbl_comments 
INNER JOIN tbl_users 
on tbl_comments.user_id = tbl_users.user_id 
and  tbl_comments.thread_id= 10
INNER JOIN tbl_votes 
on tbl_votes.comment_id = tbl_comments.comment_id 
WHERE tbl_votes.user_id= 3

虽然您的问题提到了一个名为 tbl_threads 的表，但您没有在您的示例中显示对它的任何引用。