SQLTeam.com | Weblogs | Forums

Statement to extract only a unique record matching a condition

I have a table with multiple customer records. Each customer has multiple records (different product ownership quantities). Some of these customers are 'dummy' customers. I need a Sql query to show me the records where ONLY the dummy customers have quantities in any of the products, and not any records where dummy and real customers have ownership records. In other words, I need to see the records where the dummy customers are the only ones having an ownership of a product where all of the real customers have no ownership.

Tried the is null and is not null statement but this merely shows the records matching that one condition. I also tried the 'WHERE EXISTS' but no luck.

I expect the output to show me the records of the 'FUTUR' dummy customer where it holds any quantity of a product where all of the Real customers have no holdings.

Something like this:

SELECT 
	ProductId
FROM
	YourTable
GROUP BY 
	ProductId
HAVING
	SUM(CASE WHEN CustomerType = 'Real' THEN 1 ELSE 0 END) = 0
	AND SUM(CASE WHEN CustomerType = 'Dummy' THEN 1 ELSE 0 END) <> 0