Search code examples
mysqlsqljoin

SQL Query to select customers who have already purchased all the items from their Wishlist

I am trying to write a query and as it is a bit complex, I am not able to get the right results.

Goal of the query: Get list of Customer who have bought all the items from their Wishlist?

I have 2 tables and the fields as given below,

  1. Customer_Orders : Customer_ID, Order_ID, Order_date , Product_ID, email

  2. Wishlist : Customer_ID, Created_Date , Product_ID

I was only able to write the below query, which just checks the products customer has bought from Wishlist

select w.Customer_ID,w.Product_ID
from Wishlist w
inner join Customer_Orders od
on od.Product_ID=w.Prodcuct_ID and od.Customer_ID=w.Customer_ID

What I want instead is to fetch those customers(Customer_ID) who have bought all the items from their Wishlist.

Please can anyone help me know how the query should be ??

Solution

  • yes customer_id, product_id is defined as unique in both the tables – sfmc_newbie

    SELECT Customer_ID
FROM Wishlist
LEFT JOIN Customer_Orders USING (Customer_ID, Product_ID)
GROUP BY Customer_ID
HAVING !SUM(Customer_Orders.Product_ID IS NULL)

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=352142a37fd1aaddcc6d3c20ef886d30