Query help - 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

thebrenda
Starting Member

22 Posts

Posted - 2012-10-12 : 05:35:46

my data is below. i want to find accounts that are related to more than one customer. each account should only belong to a single customer, but i suspect that is not the case. in my examaple below i want to find accounts 2 and 4.

customer account
111111 1
111111 1
111111 2
222222 2
333333 3
333333 4
444444 4

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-12 : 05:46:27

DECLARE @tab TABLE(customer bigint, account int)
INSERT INTO @tab
SELECT 111111, 1 UNION ALL
SELECT 111111, 1 UNION ALL
SELECT 111111, 2 UNION ALL
SELECT 222222, 2 UNION ALL
SELECT 333333, 3 UNION ALL
SELECT 333333, 4 UNION ALL
SELECT 444444, 4
SELECT account, MIN(customer) FROM @tab
GROUP BY account
HAVING COUNT(distinct customer) > 1

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-12 : 05:46:49

Try this:

select distinct account
from YourTable as t1
where exists(select * from YourTable as t2 where t2.account=t1.account and t2.customer <> t1.customer)

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources