find the customers who bought both products A and B but did not buy product C. To understand we will consider the below customers and purchases table:
-- Create the table
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name NVARCHAR(100)
);
-- Insert the data
INSERT INTO Customers (customer_id, name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Alice Johnson'),
(4, 'Bob Brown'),
(5, 'Charlie Black');
-- Create the table
CREATE TABLE Purchases (
chase_id INT PRIMARY KEY,
customer_id INT,
product_id CHAR(1)
);
-- Insert the data
INSERT INTO Purchases (chase_id, customer_id, product_id) VALUES
(1, 1, 'A'),
(2, 1, 'B'),
(3, 2, 'A'),
(4, 2, 'C'),
(5, 3, 'B'),
(6, 3, 'A'),
(7, 4, 'A'),
(8, 4, 'B'),
(9, 5, 'C');
Method 1:
SELECT c.customer_id, c.name
FROM Customers c
WHERE
c.customer_id IN (
SELECT p.customer_id
FROM Purchases p
WHERE p.product_id = 'A'
)
AND c.customer_id IN (
SELECT p.customer_id
FROM Purchases p
WHERE p.product_id = 'B'
)
AND c.customer_id NOT IN (
SELECT p.customer_id
FROM Purchases p
WHERE p.product_id = 'C'
);
Method 2 :
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (
SELECT customer_id
FROM purchases
WHERE product_id = 'A'
AND customer_id IN (SELECT customer_id FROM purchases WHERE product_id = 'B')
AND customer_id NOT IN (SELECT customer_id FROM purchases WHERE product_id = 'C')
);
Method 3:
SELECT c.customer_id, c.name
FROM Customers c
INNER JOIN Purchases p1 ON c.customer_id = p1.customer_id AND p1.product_id = 'A'
INNER JOIN Purchases p2 ON c.customer_id = p2.customer_id AND p2.product_id = 'B'
LEFT JOIN Purchases p3 ON c.customer_id = p3.customer_id AND p3.product_id = 'C'
WHERE p3.chase_id IS NULL;
Method 4:
SELECT Distinct customer_id, name FROM
(
select a.customer_id,Name ,STRING_AGG(product_id,',') as Product_Purchase
from Purchases a
INNER JOIN Customers b ON a.customer_id =b.customer_id
group by a.customer_id,Name
)A
CROSS APPLY STRING_SPLIT(Product_Purchase, ',') AS s
WHERE
CHARINDEX('A', Product_Purchase) > 0 AND
CHARINDEX('B', Product_Purchase) > 0 AND
CHARINDEX('C', Product_Purchase) = 0;
Leave a Reply