Help with simple SQL query - 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 with simple SQL query

Author

Topic

mlazur
Starting Member

5 Posts

Posted - 2010-08-23 : 11:48:26

I have a table which is basically a list of people names from larger databases. Each record is an individual with the source in which they came from. Each individual is also assigned an ID. From some matching logic it was determined whether there were multiple instances of a person in the table (coming from different sources).

For instance. John Smith from DB1 and John Smith from DB2 would be given the same ID and would appear as two records.

ID NAME SOURCE
1001 John Smith DB1
1001 John Smith DB2
1002 Kevin Toll DB2
1002 Kevin Toll DB3
1002 Kevin Toll DB1
1003 Mike Gutt DB3

I am trying to figure out a way to determine how many IDs are only from 1 DB, how many are from 2 DBs, and how many are from 3 DBs.

EG i would like to know what from this example that there is 1 ID from 2 Databases (DB1 & DB2), there is 1 ID from 3 DBS, and there is 1 ID from 1 DB.

Any help at all would be great. Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-08-23 : 11:52:33

select ID, name, count(1) FROM <table> Group by ID, name

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-23 : 11:53:55

This will show you all the IDs with multiples


SELECT	ID, Count(SOURCE) c
FROM	YourTable
GROUP BY
	ID
HAVING	Count(SOURCE) > 1

This will show you the records


SELECT	t.*
FROM	YourTable t
JOIN	(
	SELECT	ID, Count(SOURCE) c
	FROM	YourTable
	GROUP BY
		ID
	HAVING	Count(SOURCE) > 1
) x
On	x.ID = t.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources