How to get the customers who bought x product also bought y product

Question

I'm dealing with multiple datasets.

sales(cust, product_no, total_amount)
product(product_no, product_quantity)
X_product_list(X_product_no)
Y_product_list(Y_product_no)

In product table we have two columns product_no, product_quantity. product_no contains X_product_no and Y_product_no

Note - Both X_product_list and Y_product_list doesn't match as they are different products.

I'm doing inner join on Sales product_no and Product product_no. Then trying to check customers who bought X_product_list i.e. with X_product_no did also bought Y_product_list i.e. Y_product_no. Below is the Snowflake SQL query which i've written. It's returning empty data.

SELECT 
sales.cust,
FROM sales
INNER JOIN product
      ON sales.product_no = product.product_no
WHERE sales.product_no IN (SELECT X_product_no FROM X_product_list)
      AND sales.product_no IN (SELECT Y_product_no FROM Y_product_list)

I'm trying to get the X_product_no customers who also bought Y_product_no items.

Answer 1

Using INTERSECT:

SELECT sales.cust
FROM sales
JOIN product
  ON sales.product_no = product.product_no
WHERE sales.product_no IN (SELECT X_product_no FROM X_product_list)
INTERSECT
SELECT sales.cust
FROM sales
JOIN product
  ON sales.product_no = product.product_no
WHERE sales.product_no IN (SELECT Y_product_no FROM Y_product_list)