CODEWITHSUNDEEP
mysql
Navya Rp
Navya Rp

Reputation: 3

list of Product customer didn't ordered?

I'm stuck one query here. I need see that which products customer X hasn't bought. Ie, which products 'Jane D'(customer) has not ordered yet. Below is database design. And while coding I ended up getting which product this customer has ordered but couldn't get which he/she didn't ordered. Can you please help?

What i wanted is product list that are in inventory and customer has not ordred. not the one from product table

Customer: customer_id, customer_name

Order: order_id, customer_id,inventory_id, order_Date

Inventory: Inventory_id, product_ID

Product: product_id, product_name

from below query i can find what customer_id=1 has bought


SELECT p.Product_ID, p.Product_NAME
FROM CUSTOMER C JOIN Order O ON c.customer_id=o.customer_id
JOIN INVENTORY I ON o.inventory_id=i.inventory_id
JOIN product p ON i.product_id=p.product_id
WHERE C.CUSTOMER_ID='1';

Upvotes: 0

Views: 189

Answers (2)

Mark Byers
Mark Byers

Reputation: 838296

SELECT p.Product_ID, p.Product_NAME
FROM product p 
JOIN INVENTORY i ON p.product_id = i.product_id
LEFT JOIN Order o
        ON o.inventory_id = i.inventory_id
        AND o.customer_id = '1'
WHERE o.customer_id IS NULL

Upvotes: 2

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

The below query would give the required result. The right outer join along with the null check does the trick for you.

SELECT p.Product_ID, p.Product_NAME
FROM CUSTOMER C JOIN Order O ON c.customer_id=o.customer_id
JOIN INVENTORY I ON o.inventory_id=i.inventory_id
RIGHT OUTER JOIN product p ON i.product_id_id=p.product_id
WHERE C.CUSTOMER_ID='1' and i.product_id is null;

Upvotes: 0

Related Questions