SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select id from XXX where count (addcode of id)=0..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sishan81
Starting Member

7 Posts

Posted - 08/28/2012 :  10:44:15  Show Profile  Reply with Quote
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

USA
15557 Posts

Posted - 08/28/2012 :  10:51:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT SenderID FROM myTable A
WHERE NOT EXISTS(SELECT * FROM myTable WHERE SenderID=A.SenderID AND SenderAdditionalCode=3)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 08/28/2012 :  10:53:56  Show Profile  Reply with Quote
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
	

Edited by - sunitabeck on 08/28/2012 10:54:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000