CODEWITHSUNDEEP
mysqlsql
Ramesh S
Ramesh S

Reputation: 804

Mysql select query for 3 tables

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.

Upvotes: 0

Views: 72

Answers (3)

jarlh
jarlh

Reputation: 44696

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

Upvotes: 1

Matt
Matt

Reputation: 15061

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

Upvotes: 1

Alexandre Elshobokshy
Alexandre Elshobokshy

Reputation: 10922

@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"

Upvotes: 0

Related Questions