Duplicate Records Help - 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

Duplicate Records Help

Author

Topic

awsachsen
Starting Member

3 Posts

Posted - 2015-02-20 : 13:48:44

Hi all,

I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:

select *
  from VendTable1 a
  join ( select firstname, lastname 
           from VendTable1 
          group by firstname, lastname 
         having count(*) > 1 ) b
    on a.firstname = b.firstname
   and a.lastname = b.lastname

I'm running into issues when trying to add the other table with the 4 other fields. Not sure how to do this? Please advise if possible!

Thank you!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 14:45:38

You need to let us see the schema of the other table.

awsachsen
Starting Member

3 Posts

Posted - 2015-02-20 : 15:04:16

quote:
Originally posted by gbritton

You need to let us see the schema of the other table.

the two tables are actually: stg.na_LFA1 and stg.na_lfb1. I just used a dummy table up top. Please let me know if you need anything else

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 15:16:00

So...do both tables have exactly the same definitions? (same number, types and names of columns?) This is why we usually ask for the CREATE TABLE commands for all tables involved in the queries.

Also, some sample data for each table with expected results using that data would help a lot

awsachsen
Starting Member

3 Posts

Posted - 2015-02-21 : 16:04:44

Sorry, I'll try to shed some more light on what I'm asking with some examples:

Table 1: stg.na_LFA1

Vendor # Company Code Contact Name Phone Number

256333 0001 John Smith 555-333-4444
234555 0001 John Smith 555-333-4444
342344 0002 Tom Jones 654-454-3334
345345 0002 John Smith 555-333-4444

Table 2: stg.na_lfb1

Vendor # Account Grp City Country

256333 APMT St. Paul US
234555 APMT St. Paul US
342344 MRO Minneapolis US
345345 APMT St. Paul US

I want the output to be (duplicates):

Vendor # Company Code Account Grp City Country

256333 0001 APMT St. Paul US
234555 0001 APMT St. Paul US

So for this example, I'd only want to pull the duplicates based on company code, account group, City, and Country but still show the vendor number. The two tables tie together by the vendor #.

Please let me know if you have any questions/concerns.

quote:
Originally posted by gbritton

So...do both tables have exactly the same definitions? (same number, types and names of columns?) This is why we usually ask for the CREATE TABLE commands for all tables involved in the queries.

Also, some sample data for each table with expected results using that data would help a lot

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-21 : 17:10:23

Looks like a simple join:

select a.vendoe, a.companyCode, b.Account, b.grp, b,city, b,country
from stg.na_LFA1 a
join stg.na_lfb1 b
on a.Vendor = b.Vendor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources