AND NOT EXISTS ? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

AND NOT EXISTS ?

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-29 : 07:52:52

Hi All

I am trying achieve this sort of logic in a query...


SELECT
	FirstName,
	LastName
FROM
	Names
INNER JOIN
	cmp ON Names.ID = cmp.ID
WHERE
	cmpID IN (1,2,3,4)
AND
	cmpID NOT IN (5,6,7,8)

I am wondering if i need to use some sort of NOT EXISTS instead of the NOT IN part of the where clause

Any pointers greatly appreciated

====
Paul

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-29 : 07:56:37

No - but yoou also don't need the and

WHERE cmpID IN (1,2,3,4)
is the same as
WHERE cmpID IN (1,2,3,4)
and cmpID not in (5,6,7,8)
is the same as
WHERE cmpID between 1 and 4 -- assuming it is an integer

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-29 : 08:16:52

Sorry my example wasn't very good

The query is actually more complex than that.

We have people who cover multiple cmpIDs (campaign IDs) and i want to exclude certain cmpIDs.

For example

Joe Bloggs may cover cmpIDs 1,2,3,4,6,7
and John Doe may cover cmpIDs 1,2,3,4

i would like to see only people who have covered cmpIDs 1,2,3,4 and exclude any one who covers cmpIDs 5,6,7,8

So Joe Bloggs would not show in the results even though he covers cmpIDs 1-4

====
Paul

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-29 : 08:58:37

May be you do need to use EXISTS and/or NOT EXISTS constructs. For example, may be this?

SELECT
	FirstName,
	LastName
FROM
	Names n
WHERE
	EXISTS 
		(SELECT * FROM cmp c WHERE c.cmpId = n.cmpId AND c.cmpId IN (1,2,3,4))
	AND NOT EXISTS
		( SELECT * FROM cmp c WHERE c.cmpId = n.cmpId AND c.cmpId IN (5,6,7,8));

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-29 : 09:09:59

Thanks - i'll give that a go

quote:
Originally posted by sunitabeck

May be you do need to use EXISTS and/or NOT EXISTS constructs. For example, may be this?
SELECT
FirstName,
LastName
FROM
Names n
WHERE
EXISTS
(SELECT * FROM cmp c WHERE c.cmpId = n.cmpId AND c.cmpId IN (1,2,3,4))
AND NOT EXISTS
( SELECT * FROM cmp c WHERE c.cmpId = n.cmpId AND c.cmpId IN (5,6,7,8));

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources