SQL Query Returning Multiple Lines--and Join doesn't appear to be correct

I have the following SQL Code

select distinct 'ORG', h.Name, CAST(h.Birthdate as DATE), h.SocialSecurityNumber, p.Provider_UnvUserID, p.Provider_UnvUserID, adx.AbstractDiagnosisCode_MisDxID AS ICD9, 
r.AccountNumber AS ACCOUNTID, ads.DischargeDateTime AS DISCHARGEDATE
 from [CYD-DR01].[livefdb].[dbo].[RegAcct_Main] r
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_Providers] p ON p.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_ProviderData] pd ON pd.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[HimRec_Main] h ON h.PatientID = r.PatientID
JOIN [CYD-DR01].[livefdb].[dbo].[AbsAcct_Diagnoses] ad ON ad.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[RegAcct_AbsDxs] adx ON adx.VisitID = r.VisitID
JOIN [CYD-DR01].[livefdb].[dbo].[MisDx_Main] mdm ON mdm.MisDxID = adx.AbstractDiagnosisCode_MisDxID
JOIN [CYD-DR01].[livendb].[dbo].[AdmDischargeInfo] ads ON ads.VisitID = r.VisitID
where r.Location_MisLocID = 'MEDSURG' and p.ISPrimaryCareProvider = 'Y' and pd.PrimaryCareProvider_UnvUserID IN ('DOC1', 'DOC2', 'DOC3', 'DOC4') 
and ad.DiagnosisUrnID = 1 
order by h.Name desc

Open in new window

I am getting the data I expect--however the last part of my where clause "And ad.DiagnosisUrnID = 1" doesn't appear to be working--and I am getting several entries for each patient, each with a unique diagnosis.  If I add the DiagnosisUrnID to the select statement, every line shows a 1--however if I go into that table "AbsAcct_Diagnosis", I can see the proper numbers behind the lines (1, 2,3).  Essentially, I'm trying to pull only the primary diagnosis for those patients.

I'm joining on Visit ID--which appears to be working--so I'm unsure why it's returning a ad.DiagnosisUrnID of 1 on each line when that doesn't match that table.  Thanks for any help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The WHERE condition should work just fine, and I believe it is.  However, to be sure, you could add it as a condition on the JOIN as well:

JOIN [CYD-DR01].[livefdb].[dbo].[AbsAcct_Diagnoses] ad ON ad.VisitID = r.VisitID AND ad.DiagnosisUrnID = 1
Kyle AbrahamsSenior .Net DeveloperCommented:
Can the DiagnosisURNId be null?

and isnull(ad.DiagnosisUrnID,1) = 1 

Open in new window

CMCITDAuthor Commented:
Tried that join and the Null Exclusion--no difference.  I don't see any Nulls in that table.  Attached a photo to show the behavior.  Top photo of the table is what I see when I do my massive Join Query.

Bottom part of the photo is the specific table, doing a select using that account.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kyle AbrahamsSenior .Net DeveloperCommented:
Is visitId the only FK to RegAcct?  
select r.AccountNumber, ad.DiagnosisUrnID 
from [CYD-DR01].[livefdb].[dbo].[RegAcct_Main] r
JOIN [CYD-DR01].[livefdb].[dbo].[AbsAcct_Diagnoses] ad ON r.VisitID = ad.VisitID
where ad.DiagnosisUrnID = 1

Open in new window

If you do this do you get expected results?
what's the relationship between AD and ADX?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Truly odd when there's no "OR" condition in the WHERE.

Are you sure you're pointing at the right db?  (And instance??)

I noticed the table you showed is upper case and the results are mixed case.  If the query didn't modify the case, that doesn't make sense? Unless the wrong table was read somehow??
CMCITDAuthor Commented:
That actually did it.  I'm converting old queries over to the new tables--and didn't realize--I didn't even need the RegAcct_AbsDxs table anymore as the new table held the value needed (that was the ADX table you were referencing).  Once I commented out that table, it displays the primary diagnosis only.  

I'll give your solution full credit--but first do you mind explaining why that join was breaking the query?  I don't see why the adx table would do that.

Thank you!
Kyle AbrahamsSenior .Net DeveloperCommented:
it's the join of the two tables having similar data that was the issue.

assuming you have the same thing on ADX
you could get away with:

and ad.DiagnosisUrnID = 1  and adx.DiagnosisUrnID = 1 

Open in new window

To answer your question though you were filtering on the ad table but not adx table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.