Additional join causing duplicates

Question

Additional join causing duplicates

krypto69

SSChampion

Points: 13604
More actions
March 9, 2015 at 1:48 pm

#315352

Hi,
I have this statement:
SELECT top 100 P.LastName ,
P.FirstName,
P.MiddleName,
P.PractitionerTypeID,
P.SuffixID,
A.Linenumber1,
A.LineNumber2,
Z.City,
Z.State,
Z.ZipCode,
Z.County,
P.NationalProviderID,
PIS.LegalName,
PLS.LocationName
FROM dbo.Practitioners AS P LEFT OUTER JOIN
dbo.PractitionerLocations AS PL ON P.PractitionerID = PL.PractitionerID LEFT OUTER JOIN
dbo.PracticeLocations AS PLS ON PL.LocationID = PLS.LocationID LEFT OUTER JOIN
dbo.Addresses AS A ON PLS.AddressID = A.AddressID LEFT OUTER JOIN
dbo.ZipCodes AS Z ON A.ZipCodeID = Z.ZipCodeID
It returns the correct records but when I add a join - it creates duplicates. I have tried a few different joins like this:
FROM dbo.Practitioners AS P LEFT OUTER JOIN
dbo.PractitionerLocations AS PL ON P.PractitionerID = PL.PractitionerID LEFT OUTER JOIN
dbo.PracticeLocations AS PLS ON PL.LocationID = PLS.LocationID LEFT OUTER JOIN
dbo.Addresses AS A ON PLS.AddressID = A.AddressID LEFT OUTER JOIN
dbo.ZipCodes AS Z ON A.ZipCodeID = Z.ZipCodeID RIGHT OUTER JOIN
dbo.Practices AS PIS ON PLS.PracticeID = PIS.PracticeID[/code]
Seems like no matter which join type I choose i still get duplicates. Not sure what I am not seeing here.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a practitioner could have more than one location; you know, he works at Baptist East, Baptist North and Baptist South, so that would be normal to see the same provider multiple times, due to locations.

if your locations data was not clean, meaning there was more than one duplicate address, that would also explain "duplicates";

so if someone had inserted Baptist North as a location twice.

so are you seeing true duplicates, or data you don't expect /want to see?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Check for uniqueness of PracticeID in dbo.Practice

_____________
Code for TallyGenerator

krypto69 SSChampion Points: 13604 More actions · Answer 3

Thanks Lowell and Sergiy.

I assumed the ID values were being forced they were not.

Thanks again guys.

krypto69 SSChampion Points: 13604 More actions · Answer 4

Lowell (3/9/2015)
a practitioner could have more than one location; you know, he works at Baptist East, Baptist North and Baptist South, so that would be normal to see the same provider multiple times, due to locations.
if your locations data was not clean, meaning there was more than one duplicate address, that would also explain "duplicates";
so if someone had inserted Baptist North as a location twice.
so are you seeing true duplicates, or data you don't expect /want to see?

Hi Lowell,

I need to use this data to import into a different Database via SSIS (or script). So, you I think you are spot on the practitioner is listed 10 times because he has 10 offices. But in a SSIS package how would I import the doctor one time and his offices 10 times? Is there a way in SSIS that can handle that senario? Or do I need to break it in the script somehow?

Lowell SSC Guru Points: 323518 More actions · Answer 5

yes, it's certainly possible.

i was thinking import down bloew but you mena tto export.

you want to select distinct provider, base don NPI number, as oneexport, adn a seperate export for locaitons, i hthink.

this was my old thoughts, where i was thinking IMPORT.

if you are not using a staging table in SQL, where you could run a group by, and then a seperate select, you can use a multi cast.

you multicast to an aggregate to group by say, NPI number of the physician, which eliminates dupes, and a seperate cast to the locations

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!