Advice on joining three tables

Question

Advice on joining three tables

I am trying to join multiple tables as I need the values that are in all of them.

I tried two ways:

First way:

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)

Second way:

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

Table data:

Orders

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

orderproducts

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

clients

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

Sorry but I don't know how to present the data I want, I will try to explain it.

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

0

sql join mysql

Filipe Teles Apr 28 '15 at 6:29

source to share

1 answer

Abhik chakraborty · Accepted Answer · 2015-04-28T07:01:07+0000

You need to join with orders

and customer

with customer_id

and it is always better to connect with ids.

So the request would be

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)

Now what will this do

It will try to join all tables if there is matching data, i.e. if the same order_id

is on orders

and in orderproducts

, and the same customer_id

is in orders

andcustomers
Finally, filter the data only up to 616898 or 616901

Note that if there is no matching data for a given filter, you may not get the result. If you still want the data to be returned from the table orders

even if there is no match and concatenating the table data as null, you may need to change inner join

toleft join

Advice on joining three tables

More articles: