select id from XXX where count (addcode of id)=0..

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

General SQL Server Forums

New to SQL Server Programming

select id from XXX where count (addcode of id)=0..

Author

Topic

sishan81
Starting Member

7 Posts

Posted - 2012-08-28 : 10:44:15

Hi,
I'm not sure how to perform a query.
I have a table with 2 columns: SenderID and SenderAdditionalCode.
Each SenderID can have multiple SenderAdditionalCodes.
If I want to get a list of all SenderID's that don't have SenderAdditionalCode=3, how can I do it?

I'm lost, and would appreciate any help.
Thx!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-28 : 10:51:13

SELECT SenderID FROM myTable A
WHERE NOT EXISTS(SELECT * FROM myTable WHERE SenderID=A.SenderID AND SenderAdditionalCode=3)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 10:53:56

One of these:


SELECT
	*
FROM
	theTable t1
WHERE
	NOT EXISTS 
	(
	    SELECT
	    	*
	    FROM
	    	theTable t2
	    WHERE
	    	t2.SenderId = t1.SenderId
	    	AND t2.SenderAdditionalCode = 3
	);
	
	
SELECT
	SenderId
FROM
	theTable
GROUP BY
	SenderId
HAVING
	SUM(CASE WHEN SenderAdditionalCode = 3 THEN 1 ELSE 0 END) = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources