SELECT distinct Customer_Number, SUM(Price) As Total
FROM Customer_Offer x1
WHERE
	EXISTS 
		(SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number 
			AND x2.Product_Offer = 'prod1')
	AND NOT EXISTS
		(SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND 
			x3.Product_Offer IN ( 'prod2','prod3', 'prod4') )
			GROUP BY Customer_Number
			ORDER BY Customer_Number;

SELECT distinct Customer_Number, SUM(price) As Total
FROM Customer_Offer x1
WHERE
	EXISTS 
		(SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number 
			AND x2.product_offer IN ('prod1', prod2)
	AND NOT EXISTS
		(SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND 
			x3.product_offer IN ( 'prod3','prod4') )
GROUP BY Customer_number
ORDER BY Customer_Number;
			

quote:

---

Originally posted by soulchyld21

This code works perfectly,

SELECT distinct Customer_Number, SUM(Price) As Total
FROM Customer_Offer x1
WHERE
	EXISTS 
		(SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number 
			AND x2.Product_Offer = 'prod1')
	AND NOT EXISTS
		(SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND 
			x3.Product_Offer IN ( 'prod2','prod3', 'prod4') )
			GROUP BY Customer_Number
			ORDER BY Customer_Number;

however I would like to find customers who have bought prod1 and prod2 and not bought prod3 and prod4, I tried using this code

SELECT distinct Customer_Number, SUM(price) As Total
FROM Customer_Offer x1
WHERE
	EXISTS 
		(SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number 
			AND x2.product_offer IN ('prod1','prod2') -- missed the single quotes
	AND NOT EXISTS
		(SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND 
			x3.product_offer IN ( 'prod3','prod4') )
GROUP BY Customer_number
ORDER BY Customer_Number;
			

but get a syntax error! is anyone able to point me in the right direction for what I am trying to achieve?

---

    EXISTS		(SELECT * FROM Customer_Offer p1 
				  WHERE x1.customer_number = p1.customer_number 
				    AND p1.product_offer = 'prod1')
AND EXISTS		(SELECT * FROM Customer_Offer p2 
				  WHERE x1.customer_number = p2.customer_number 
					AND p2.product_offer = 'prod2')
AND NOT EXISTS	        (SELECT * FROM Customer_Offer p3 
				  WHERE x1.customer_number = p3.customer_number 
				    AND p3.product_offer IN ('prod3','prod4'))