locating duplicates accross multiple tables

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 2000 Forums

Transact-SQL (2000)

locating duplicates accross multiple tables

Author

Topic

simonw2003
Starting Member

11 Posts

Posted - 2006-01-18 : 06:45:50

I searched the forums and found many topics on removing duplcaites.

I need to do the opposite - I'd like to display a list of duplicates for a few tables so that I can then manually decide on how to deal with them (e.g. which table gets to keep the entry.

So here is a simplfied version of my problem:

- TableA TableB TableC TableD all have a column with phone numbers listed.

- I want to return a list of phone numbers which exist more than once, along with their table name.

- The phone numbers may not be unique even in their own table.

- Not sure it matters but these tables would exist in different sql databases on the same server.

Many thanks
Simon

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 07:06:16

Select phoneNo,count(*) as Counting from yourTable
group by phoneNo

Madhivanan

Failing to plan is Planning to fail

simonw2003
Starting Member

11 Posts

Posted - 2006-01-18 : 07:26:35

Thanks madhivanan

Im ok with dealing with one table.

What about if there are several tables (perhaps 4) which need entries comparing? Thats where I am stuck - if anyone can assist.

I guess my end result would be best defined as a list of phonenumbers that appear in more than one table, ideally with the table assocaited names)

I dont mind if it requires temp tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 07:53:53

See if this helps
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-18 : 09:07:03

Use UNION ALL to union the various tables you need to check together into 1 large resultset. Add a column to label each table's data with their name. Like this:

select 'Table 1' as TableName, PhoneNumber
from Table1
union all
select 'Table 2' as tableName, PhoneNumber
from Table2
..etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources