Tip on joining three tables

Question

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:

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

Tables 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 | |

customers

+----------------------------+------------+------+-----+ | 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.

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.

can you please provide Sample table data and your Expected output? — Chiragkumar Thakar
– Chiragkumar Thakar, Commented Apr 28, 2015 at 6:33
i think the problem is with c.email = o.email condition. Can you please tell me more about that?? Why you added that condition? — Fathah Rehman P
– Fathah Rehman P, Commented Apr 28, 2015 at 6:58
Your second way is deprecated. ANSI SQL supports explicit joins for over 20 years now, so there really is no need to keep working the old fashion way. — Zohar Peled
– Zohar Peled, Commented Apr 28, 2015 at 7:10

Abhik Chakraborty · Accepted Answer · 2015-04-28 07:01:07Z

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

So the query 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 this will do

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
Finally filter data only to 616898 or 616901

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

As I said you are filtering data for 616898, 616901 so check if the same order_id is on both orders and orderProducts if yes then check if the same customer_id for above order numbers are in both orders and customers if yes then check if there are some emails against those customers and if yes the above query will return data. But if any of the joining clause is failed in inner join then you will miss that record.

Collectives™ on Stack Overflow

Tip on joining three tables

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related