mysql - Mysql选择查询3个表

Question

我有 3 个表订单，order_option，product_option

命令

order_id | cus_name | cus_phone
-------------------------------
1        | Test-1   | 9876543211
2        | Test-2   | 9876543212
3        | Test-3   | 9876543213
4        | Test-4   | 9876543214

order_option

product_option_id | order_id 
-------------------------------
11                | 1
12                | 1
13                | 2
14                | 4
15                | 3

产品选项

product_id | product_option_id | sku     | qty
------------------------------------------
1          | 11                | TS01    | 3
2          | 12                | TS02    | 2
3          | 13                | TS033   | 3
4          | 14                | TS023   | 3

在这里，我想在字段上选择带有 where 条件的order表和表值。product_optionsku

我尝试加入如下查询：

SELECT o.order_id, o.cus_name, o.cus_phone,po.sku,po.qty FROM order o 
LEFT JOIN order_option op 
ON (o.order_id = op.order_id) 
LEFT JOIN product_option po
ON (op.product_option_id = po.product_option_id) 
WHERE po.sku = "TS023"

但它没有显示正确的答案。我不知道我错过了什么。

标签： mysqlsql

Answer 1

order是保留字，使用反引号``。

SELECT o.order_id, o.cus_name, o.cus_phone, po.sku, po.qty 
FROM `order` o 
LEFT JOIN order_option op ON o.order_id = op.order_id
LEFT JOIN product_option po ON op.product_option_id = po.product_option_id
WHERE po.sku = "TS023"

输出：

order_id    cus_name    cus_phone   sku     qty
4           Test-4      9876543214  TS023   3

SQL 小提琴：http ://sqlfiddle.com/#!9/9b76b/2/0