MySQL 三个表 INNER JOIN 和 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15188234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Three Tables INNER JOIN and WHERE Clause
提问by Rashid Farooq
I have four tables 1. tbl_threads 2. tbl_comments 3. tbl_votes 4. tbl_users
我有四个表 1. tbl_threads 2. tbl_comments 3. tbl_votes 4. tbl_users
suppose the currently logged in user_id =3 thread_id = 10
假设当前登录的 user_id =3 thread_id = 10
Now I have to retrieve the following data
现在我必须检索以下数据
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
How can I perform all this function with one single query?
如何通过一个查询执行所有这些功能？
I have tried the following query but it gives me the wrong results
我尝试了以下查询，但它给了我错误的结果
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
回答by BellevueBob
Assuming the column thread_idis in the tbl_commentstable, change the first whereto and:
假设该列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
And although your question mentions a table named tbl_threads, you don't show any reference to it in your example.
尽管您的问题提到了一个名为 的表tbl_threads，但您没有在示例中显示对它的任何引用。
回答by Hariom
SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date,
P.ProductName, I.Quantity, I.UnitPrice
FROM [Order] O
JOIN OrderItem I ON O.Id = I.OrderId
JOIN Product P ON P.Id = I.ProductId
ORDER BY O.OrderNumber
