need help with query dealing with one table

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)

need help with query dealing with one table

Author

Topic

majik
Starting Member

2 Posts

Posted - 2012-01-27 : 01:16:47

I need help in crafting a query. I am working with only one table. The table has the following columns
- account_no (unique)
- ssn
- balance_due
- other columns irrelevant to the query criteria

This table has some record where balance_due = 0.00.
It also has some records where balance_due > 0.00.

People (based on SSN) may have accounts with balance_due = 0.00 while other accounts of have balance_due > 0.00.

For example:
123, 444556666, 100.00
456, 444556666, 0
789, 777889999, 0
369, 333445555, 50.00
885, 555779999, 75.00
246, 555779999, 22.21

I need help creating a query that will return a list of records whose balance_due>0.00 for only those ssn’s that also have an account blance_due=0.00.

My goal is to identify people who have an account with a balance_due but also have other accounts with no balance_due. I want to send then notification such as “Your accounts with no balance_due have been closed, please use XYZ account in the future.” In the example above, I’d like to get a list that includes “123” (bc the ssn has account 456 with balance =0.00).

Additionally I’d like to create a separate query that will return a list of records whose balance_due=0.00 but the associated ssn does not have any other accounts with a balance_due>0.00. I want to send notification “Your account XYZ has been closed because it has no balance_due”. In the example above, I’d like to get a list that includes “789” (because ssn doesn’t have any other accounts with balance_due>0.00).

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-27 : 03:36:54

select * from table as t1 where t1.balance_due > 0
and exists(select * from table as t2 where t2.ssn=t1.ssn and t2.balance_due=0)

select * from table as t1 where t1.balance_due = 0
and NOT exists(select * from table as t2 where t2.ssn=t1.ssn and t2.balance_due>0)

No, you're never too old to Yak'n'Roll if you're too young to die.