Exclude records from query by column combination

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exclude records from query by column combination

Author

Topic

vbjunkie
Starting Member

5 Posts

Posted - 2010-03-24 : 20:33:56

Can someone help me with a query that excludes results based on two combinations of columns. I'm trying to create a query based on the following logic: Exclude product records if type = "new" and status = "A" and location = "AC", if the product also contains a record with Status = "A" and location ="LA" and type <> "new". Thanks for any help with this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-24 : 20:51:39

maybe this:?

where (type = 'new' and status = 'A' and location = 'AC') or (Status = 'A' and location ='LA' and type <> 'new')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vbjunkie
Starting Member

5 Posts

Posted - 2010-03-24 : 21:11:47

I can't use an or statement because the first and second combination must exist for the record to be excluded.

For example, Product_number 200 - if a record for Product_number 200 is found with (type = 'new' and status = 'A' and location = 'AC'), I will then have to check to see if product_number 200 has another record containing (Status = 'A' and location ='LA' and type <> 'new'). If both of these combinations exist then the record with
(type = 'new' and status = 'A' and location = 'AC') will be excluded from the query. The table has multiples rows for each product_number.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-24 : 21:52:21

I see what you mean now. I don't have SQL Server in front of me at the moment, otherwise I'd work on a solution. If someone doesn't come around with a solution for you next time I'm at a computer, I'll see if I can work something up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vbjunkie
Starting Member

5 Posts

Posted - 2010-03-28 : 09:29:38

Can this be done without using a cursor?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 13:34:56

quote:
Originally posted by vbjunkie

Can this be done without using a cursor?

i think what you need is this


SELECT t.*
FROM Table t
LEFT JOIN
(
SELECT ProductID
FROM Table
GROUP BY ProductID
HAVING SUM(CASE WHEN Status = 'A' and location ='LA' and type <> 'new' THEN 1 ELSE 0 END)>0)t1
ON t1.ProductID=t.ProductID
WHERE t1.ProductID IS NULL
OR NOT(type = 'new' and status = 'A' and location = 'AC')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vbjunkie
Starting Member

5 Posts

Posted - 2010-03-30 : 20:15:03

visakh16,

I tried your solution but I'm receiving too many rows in the result. I think the problem is with the join on the group by
productID. The table has many rows with the same productID, the join after the group by would return too many rows.

Is there another way to do something like this?

SELECT *
From product p
where not exists (
select * from (
Select *
from product
where (Status = 'A' and location ='LA' and type <> 'new'))
where (type = 'new' and status = 'A' and location = 'AC')

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 21:00:00

If the table has a single column Primary Key, then you can try this:


SELECT * FROM product
WHERE PrimaryKeyField NOT IN (
	SELECT p1.PrimaryKeyField
	FROM product p1
	INNER JOIN product p2
		ON p1.Product_number = p2.Product_number
	WHERE  p1.type = 'new'
		AND p1.status = 'A'
		AND p1.location = 'AC'
		AND p2.type <> 'new' 
		AND p2.Status = 'A'
		AND p2.location ='LA' )

The inner select selects the primary key for the records you want to exclude. Then outer query selects all records not in that list. If there's no single column Primary Key, then try this:


SELECT p1.* 
FROM product p1
LEFT JOIN  (
	SELECT p1.Product_number, p1.type, p1.status, p1.location
	FROM product p1
	INNER JOIN product p2
		ON p1.Product_number = p2.Product_number
	WHERE  p1.type = 'new'
		AND p1.status = 'A'
		AND p1.location = 'AC'
		AND p2.type <> 'new' 
		AND p2.Status = 'A'
		AND p2.location ='LA' ) p2
	ON p1.Product_number = p2.Product_number
	AND p1.type = p2.type
	AND p1.status = p2.status
	AND p1.location = p2.location
WHERE p2.Product_number IS NULL

This is why I like to have a single field primary key in tables like these.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources