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,
Customer_Orders : Customer_ID
, Order_ID
, Order_date
, Product_ID
, email
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 ??
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