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
 Help please!		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tom.mckay
Starting Member

United Kingdom
4 Posts
Posted - 06/12/2012 :  06:00:51  Show Profile  Reply with Quote
Hi all,

I have a table with three columns.

BEN
TID
Something_else

BEN contains a list of names (beneficiaries).
TID contains a list of transaction IDs.

Each beneficiary should have just ONE transaction ID. I need to see all the records (including the Something_else column) where a BEN has more than one TID.

If a particular BEN only has one TID then those records should not appear.

I've started with:

SELECT DISTINCT(BEN+TID)
FROM mytable

but the problem is that this includes all the BEN/TID pairings - I only want to see the records where there's more than one TID for a BEN.

Many thanks in advance for any ideas!

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 06/12/2012 :  06:29:51  Show Profile  Visit webfred's Homepage  Reply with Quote
select * from table where BEN in (select BEN from table group by BEN,TID having count(*) > 1)


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

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 06/12/2012 :  06:33:20  Show Profile  Reply with Quote
Query it like this:
	SELECT
		BEN,
		TID
	FROM
		myTable
	GROUP BY
		BEN,
		TID
	HAVING
		COUNT(*) > 1;
If you also want to see the Soemthing_else column, enhance it a bit like this:
SELECT a.* FROM
	myTable a
INNER JOIN 
(
	SELECT
		BEN,
		TID
	FROM
		myTable
	GROUP BY
		BEN,
		TID
	HAVING
		COUNT(*) > 1
) b ON a.BEN = b.BEN AND a.TID = b.TID;
Edited by - sunitabeck on 06/12/2012 06:33:47
Go to Top of Page

tom.mckay
Starting Member

United Kingdom
4 Posts
Posted - 06/12/2012 :  06:36:38  Show Profile  Reply with Quote
Thanks, but this returns exactly the same number of records as I started with, i.e. it still returns the records where the BEN has only one TID.

Here is some sample data:

BEN/TID/Something_else
Dave/1/Test1
Dave/1/Test2
Dave/1/Test3
Tom/9/Test13
Tom/9/Test14
Tom/8/Test15
Steve/3/Test99
Steve/3/Test98
Steve/3/Test97
Pete/5/Test23
Pete/5/Test69
Pete/6/Test73

In this case I'd need to see 6 records - i.e. all the records for Tom and Pete, as they have >1 TID associated with them. Dave and Steve should be filtered out because they each only have one TID.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 06/12/2012 :  06:42:57  Show Profile  Visit webfred's Homepage  Reply with Quote
select * from table t1 where exists(select * from table t2 where t2.BEN=t1.BEN and t2.TID<>t1.TID)

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

tom.mckay
Starting Member

United Kingdom
4 Posts
Posted - 06/12/2012 :  06:43:22  Show Profile  Reply with Quote
I'd also need to see the Something_else column too for those six records.

Thanks again
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 06/12/2012 :  06:43:54  Show Profile  Visit webfred's Homepage  Reply with Quote
yes

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

tom.mckay
Starting Member

United Kingdom
4 Posts
Posted - 06/12/2012 :  06:45:27  Show Profile  Reply with Quote
It works!

Thank you :)
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 06/12/2012 :  06:47:10  Show Profile  Visit webfred's Homepage  Reply with Quote


No, you're never too old to Yak'n'Roll if you're too young to die.
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.06 seconds. Powered By: Snitz Forums 2000