mysql - 剩下 3 个表连接任务、订单和客户但无法获取

Question

案例研究如下，

Tasks Table has an Order ID.
1 Task is belonged to an Order Table. An Order can have many tasks. Tasks Table does not have Customer ID information.

Order Table has a Customer ID.
1 Order belongs to a Customer Table. A Customer can have many orders.

现在我想生成一个任务表来显示该任务属于哪个客户。但是，我的结果是客户的列全部为 NULL。我确认我的 customer_id 存在。我是否可以不在任务表中存储客户 ID？由于订单表已经存储了客户信息。

select `tasks`.`id`, `tasks`.`created_date`, `tasks`.`due_date`, tasks.order_id, orders.id, orders.cust_id, 
customers.id, `customers`.`name`
from `tasks` 
left join `orders` on `tasks`.`order_id` = `orders`.`id` 
left join `customers` on `customers`.`name` = `orders`.`cust_id` 

是不是我错过了什么？

Answer 1

您的查询已选择客户名称，但您尝试通过客户 user_name 加入它。我建议您将它们都更改为正确的列名称(name或user_name)并尝试这样做。例如(这里我编辑后将它们与 id 匹配):

select `tasks`.`id`, `tasks`.`created_date`, `tasks`.`due_date`, tasks.order_id, orders.id, orders.cust_id, 
customers.id, `customers`.`name`
from `tasks` 
left join `orders` on `tasks`.`order_id` = `orders`.`id` 
left join `customers` on `customers`.`id` = `orders`.`cust_id` 

工作sql fiddle :http://sqlfiddle.com/#!9/5835fd1/2