Excluding records based on composite keys? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Excluding records based on composite keys?

Author

Topic

pbarrick
Starting Member

1 Post

Posted - 2008-07-01 : 11:46:30

Hi

I'm trying to write a query that will exclude records based on a primary key. However, the primary key is a composite key made up of two attributes (cid and aid).


SELECT cid, aid
FROM cli_items
WHERE 
(cid <> 1 AND aid <> 3)
AND
(cid <> 5 AND aid <> 1)

This does not work because the query excludes all records with a cid of 1 or 5 and excludes all records with an aid of 3 and 1.

I want to make the query treat cid/aid as a single entity.

Basically I need a query to do the opposite of:


SELECT cid, aid
FROM cli_items
WHERE
(cid = 1 AND aid = 3)
OR
(cid = 5 AND aid = 1)

Is it possible to do?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 14:17:45

SELECT cid, aid
FROM cli_items
WHERE
(cid <> 1 OR aid <> 3)
AND
(cid <> 5 OR aid <> 1)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-01 : 14:21:32

couple other ways:


SELECT cid, aid
FROM cli_items
WHERE NOT (cid = 1 and aid = 3)
AND NOT (cid = 5 AND aid = 1)

select ci.cid
       ,ci.aid
from   cli_items ci
left outer join (
       select 1 cid, 3 aid union all
       select 5,1
       ) ex
              on ex.cid = ci.cid
              and ex.aid = ci.aid
where  ex.cid is null

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources