CODEWITHSUNDEEP
mysqlsql
gihan
gihan

Reputation: 175

SQL query for 3 tables

I have 3 tables as follows

item{id, name, price}
customer{id, name, tel_no}
order{id, time, customer_id}
order_item{id, item_id, price, order_id}
process{id, order_item_id, status}

I need to get order_items which are not processed for a particular customer. I tried with the following query. But it doesn't help. Please correct me some one.

SELECT * 
FROM order_item`
INNER JOIN `order` ON `order`.id = order_item.order_id
WHERE `order`.customer_id=1 AND NOT EXISTS (
SELECT * 
FROM process 
WHERE process.order_item_id=order_item.id  
)

I'm using mysql as my server

Upvotes: 1

Views: 97

Answers (1)

xQbert
xQbert

Reputation: 35343

Select * 
from order_item OI
INNER JOIN ORDER O on O.ID=OI.Order_Id
LEFT JOIN Process P ON P.Order_item_ID = OI.Item_ID
where O.Customer_ID = 1 and P.ID is null

LEFT JOin gives you all the ORDER_Items and only those records with a matching process record so P.ID will be null thus the item has not been processed

Upvotes: 1

Related Questions