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?
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?
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.