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