Query to compare duplicate records

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

Transact-SQL (2005)

Query to compare duplicate records

Author

Topic

CaelDorwin
Starting Member

3 Posts

Posted - 2009-09-16 : 15:14:34

I have two tables. The fields of Table 1 are:

T1ID (pk)
Name
Description
T2ID

Table 2:
T2ID (pk)
Name

The names in Table 1 are expected to repeat across all T2ID's. Meaning if I have 10 entries in Table2, EACH distinct name in Table 1 should have a row with name, description and the appropriate T2ID.

Over time the data in the table has gotten out of alignment (shocker?) and I am trying to write a query that will take each Name from Table 1 and figure out which of the T2ID's are missing.

If that is not clear I can provide more detail but I am just plain stuck on how to get the right combination of joins/in's/not in's to make the result happen the way I need it to.

Any help would be appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 15:23:03

You mean you have T2IDs in table1 which don't exist in table2? Those are called orphaned rows. Typically a Foreign Key constraint would be on table1 (referencing table2) to not allow that.


select t1.*
from  table1 t1
left outer join table2 t2 
     on t2.T2ID = t1.T2ID
where  t2.T2ID is null

Be One with the Optimizer
TG

CaelDorwin
Starting Member

3 Posts

Posted - 2009-09-16 : 17:12:56

No I mean I have T1 data that looks like (T1ID, Name, Description, T2ID) Values:

1000, Benjamin, Sales Guy 1, 5000
1001, Benjamin, Sales Guy 1, 5001
1002, Benjamin, Sales Guy 1, 5003
1003, Tom, Sales Guy 2, 5000
1003, Tom, Sales Guy 2, 5001
1004, Tom, Sales Guy 2, 5002
1005, Tom, Sales Guy 2, 5004

And T2 data that looks like (T2ID, Name):

5000, Northeast
5001, Southeast
5002, Midwest
5003, Northwest
5004, Southwest

I need to see that Benajamin is missing from T2ID 5002 and 5004 and that Tom is missing from T2ID 5003.

Hope that helps what I am getting at.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-17 : 01:39:09

select t2.* from t2
left join t1 on t1.t2id = t2.t2id
where t1.t2id is null

select * from t2 where t2id not in (select t2id from t1)

select * from t2 where not exists (select * from t1 where t2id = t2.t2id)

CaelDorwin
Starting Member

3 Posts

Posted - 2009-09-17 : 11:11:25

At some point or another in the data each t2id is used so these queries all return no rows.

Where I am at currently is needing a query that does something like this (written in english; not syntax):

For each t1.Name:
select t2.* where t2.t2id not in
(select t1.t2id where t1.Name = [Name currently being tested])
Loop

Thanks for all of your help so far!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources