有关加入三张桌子的提示 [英] Tip on joining three tables

问题描述

我正试图联接一些表，因为我需要所有表中的值.

I'm trying to join a few tables as I'll need values that are within all of them.

到目前为止，我已经尝试了两种方法:

I've tried two ways so far:

第一种方式:

SELECT
    o.`order_id` as `Order ID`,
    o.`STATUS` as `Order Status`,
    o.`date_created` as `Date Created`,
    op.`SKU`,
    op.`NAME`,
    o.`STATE`,
    op.`QUANTITY`,
    c.`customer_id`
FROM
    `orderProducts` op
INNER JOIN
    orders o on op.order_id = o.order_id
INNER JOIN
    customers c on c.customer_id = o.customer_id
WHERE
    o.order_id IN (616898, 616901)

第二种方式:

SELECT
    o.`order_id` as `Order ID`,
    o.`STATUS` as `Order Status`,
    o.`date_created` as `Date Created`,
    op.`SKU`,
    op.`NAME`,
    o.`STATE`,
    op.`QUANTITY`,
    c.`customer_id`
FROM
    `orders` o,
    `orderproducts` op,
    `customers` c
WHERE
    o.order_id IN (616898, 616901)
AND
    c.customer_id= o.customer_id
AND
    o.order_id = op.order_id

表格数据:

订单

+----------------------------+------------+------+-----+
| Field                      | Type       | Null | Key |
+----------------------------+------------+------+-----+
| order_id                   | int(11)    | NO   | PRI |
| customer_id                | int(11)    | YES  |     |

订购产品

+----------------------------+------------+------+-----+
| Field                      | Type       | Null | Key |
+----------------------------+------------+------+-----+
| order_id                   | int(11)    | NO   |     |

客户

+----------------------------+------------+------+-----+
| Field                      | Type       | Null | Key |
+----------------------------+------------+------+-----+
| customer_id                | int(11)    | NO   | PRI |

对不起，但是我真的不知道如何表示我想要的数据，我会尽力解释它.

Sorry, but I don't really know how to represent the data I want out, I'll try my best to explain it.

我希望我的select语句中的列来自616898和616901. order和orderproducts表中的order_id字段相同. order和customers表中的customer_id字段相同.就像我要使用order_id从orderproducts表中添加额外的SKU，NAME和QUANTITY列一样，使用orders和customers表中的customer_id来提取正确的列，以及email列.

I want the columns in my select statement, from the orders 616898 and 616901. The order_id field is the same in both the order and orderproducts tables. The customer_id field is the same in both the order and customers tables. It's like I'm adding extra columns SKU, NAME, QUANTITY from orderproducts table using the order_id to extract the correct ones and the column email, using the customer_id from the orders and the customers table.

推荐答案

您需要将orders和customer与customer_id联接在一起，使用id联接通常会更好.

You need to join the orders and customer with customer_id and its always better to do joining using ids.

因此查询将是

SELECT
    o.`order_id` as `Order ID`,
    o.`STATUS` as `Order Status`,
    o.`date_created` as `Date Created`,
    op.`SKU`,
    op.`NAME`,
    o.`STATE`,
    op.`QUANTITY`,
    c.`email`
FROM
    `orders` o
INNER JOIN
    orderProducts op on op.order_id = o.order_id
INNER JOIN
    customers c on c.customer_id = o.customer_id
WHERE
    o.order_id IN (616898, 616901)

现在这将做什么

• 如果存在匹配的数据，即如果orders和orderproducts中有相同的order_id，并且orders和customers中有相同的customer_id，它将尝试连接所有表.

• It will try joining all the tables if there is a matching data i.e. if the same order_id is on orders and in orderproducts and same customer_id is in orders and customers

最后仅将数据过滤到616898 or 616901

请注意，如果没有可用于给定过滤器的匹配数据，则可能无法获得结果.如果您仍然希望即使没有匹配项也应从orders表返回数据，并且将表数据联接为null，则可能需要将inner join更改为left join

Note that if there is no matching data available for the given filter then you may not get the result. If you still want that data should be returned from orders table even if there is no match and joining table data as null you may need to change the inner join to left join

这篇关于有关加入三张桌子的提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！