table stores has two columns like store_id and products. Write a SQL query to find store_ids which sell only shampoo and biscuit

Store should sell only two products and that too only shampoo and biscuit

For example

Output:

my query: –

SELECT *
FROM (SELECT store_id AS gp_str
      FROM (SELECT store_id,
                   COUNT(product) AS prd_cnt
            FROM stores
            GROUP BY store_id) x
      WHERE x.prd_cnt = 2) y
     LEFT JOIN stores ON y.gp_str = stores.store_id;

My query gave me result of stores which sell only two products but I wanted stores which sell two products which are shampoo and biscuit only.

>Solution :

SELECT store_id
FROM stores
GROUP BY store_id
HAVING COUNT(DISTINCT product) = 2
    AND COUNT(*) = SUM(CASE WHEN product IN ('shampoo','biscuit') THEN 1 ELSE 0 END);