Mysql select query for 3 tables

Question

I have a 3 tables order, order_option, product_option

order

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_option

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

Here I want to select order table and product_option table values with a where condition on the sku field.

i tried to join the query like below:

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"

But it's not showing the correct answer. I don't know what I have missed.

I meant from your sample data. How many rows from your sample data should be returned? — Tim Biegeleisen, Jun 18 '18 at 07:50

score 1 · Answer 1 · answered Jun 18 '18 at 07:43

1

order is a reserved word, use backticks ``.

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"

Output:

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

SQL Fiddle: http://sqlfiddle.com/#!9/9b76b/2/0

answered Jun 18 '18 at 07:43

Matt

14,906
27
99
149

@RameshS Doubt about? – Matt Jun 18 '18 at 07:51
Select order list who purchase `sku = TS023` – Ramesh S Jun 18 '18 at 07:52
What is the query ? – Ramesh S Jun 18 '18 at 07:52

jarlh · Answer 2 · 2018-06-18T07:53:34.380

1

Move the po condition from WHERE to ON to get true LEFT JOIN result:

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) 
AND po.sku = "TS023"

(When in WHERE, you'll get regular INNER JOIN result.)

edited Jun 18 '18 at 07:53

answered Jun 18 '18 at 07:52

jarlh

42,561
8
45
63

score 0 · Answer 3 · answered Jun 18 '18 at 07:43

0

@Matt is correct, here's another way of doing this.

SELECT o.order_id, o.cus_name, o.cus_phone, po.sku, po.qty 
FROM `order` o, order_option op, product_option po
WHERE o.order_id = op.order_id
AND op.product_option_id = po.product_option_id
AND po.sku = "TSO23"

answered Jun 18 '18 at 07:43

Alexandre Elshobokshy

10,720
6
27
57

Don't join tables like that, you should use ANSI compliant joins. – Matt Jun 18 '18 at 07:45
@Matt it's just another way of doing it. I agree the ANSI joins should be used but it doesn't make it less correct. Reference : https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax – Alexandre Elshobokshy Jun 18 '18 at 07:48
Oracle explicitly advises to use ANSI compliant joins. – Matt Jun 18 '18 at 07:50
Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Jun 18 '18 at 07:51
@jarlh I know, if you'd read my comment ^^ But for simple queries I tend to use the old version. – Alexandre Elshobokshy Jun 18 '18 at 07:52

Mysql select query for 3 tables

3 Answers3