Help please! - 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

Help please!

Author

Topic

tom.mckay
Starting Member

4 Posts

Posted - 2012-06-12 : 06:00:51

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 06:29:51

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 06:33:20

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;

tom.mckay
Starting Member

4 Posts

Posted - 2012-06-12 : 06:36:38

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 06:42:57

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.

tom.mckay
Starting Member

4 Posts

Posted - 2012-06-12 : 06:43:22

I'd also need to see the Something_else column too for those six records.

Thanks again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 06:43:54