SQL Join Issue

I'm having a heck of a time trying to join five tables together.  The result is duplicating rows thousands of times, and putting data on rows where the data really doesn't belong.

select distinct resp.patid,
pcd.patient_name_last,
pcd.patient_name_first,
pcd.patient_name_middle,
pcd.date_of_birth,
pcd.patient_ssn,
subs.subs_policy,
resp.plan_cov_desc,
elig.last_name,
case
    when resp.plan_cov_desc = 'Title 19' and subs.guarantor_name = 'Medicaid' then 'Match'
    when resp.plan_cov_desc = 'Mental Health and Substance Abuse' and subs.guarantor_name like '%DMH%' then 'Match'
    else 'Verify'
end as Compare,
subs.guarantor_name,
subs.guarantor_id
from resp
    left outer join pcd on pcd.patid = resp.patid
    left outer join subs on subs.patid = resp.patid
    inner join guar on guar.guarantor_id = subs.guarantor_id
    left outer join elig on elig.file_name = resp.file_name and elig.segment = 'NM1'
where resp.file_name = '271SB.rsp'
and resp.plan_cov_desc not in ('DENTAL', 'MAJOR MEDICAL', 'Non Emergency Transportation', 'PHARMACY')
and subs.guarantor_id not like '9%'
and resp.elig_info = '1'
and subs.guarantor_name not in ('Non Recoverable')
and guar.inactive_guar_code = 'N'
order by resp.patid

Open in new window


I've attached a sample resultset for one person.  This person should only have two records, but is getting 74 due to join problems.  The problem presented itself when I added the elig table.  Taking that away drops my results from 10,138 records to 274.

Any ideas are much appreciated!
sample-results.xlsx
fcsITAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm having a heck of a time trying to join five tables together.
<Knee-jerk reactions>
Double check the JOIN criteria to ensure that the columns included guarantee uniqieness.
This may be by design for 1:m tables if you are further joining on the m and not the 1.  For example, orders will have one row per order, order line items will have 10 rows if you bought ten things.  Are you further JOINing on order or order line items, which would result in x10 rows returned?
Similar to the above, if you have to  join further on the m table (aka order line items) then you'll have to provide a min/max/where criteria to uniquely define the 1 row out of the 10.
Not a bad idea to copy-paste your code into another window, knock it down to two tables, then execute and make sure the number of rows are what you expect, then add the third table, then execute, etc.
0
Arun MuruganCommented:
which means the data in the elig table is duplicate. Can you post the tables structure?
0
fcsITAuthor Commented:
The elig table has about 100 columns.  (Gotta love those...)  I'm searching for a better way to join them, but the main primary key throughout the database doesn't exist in this particular table of course.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but the main primary key throughout the database doesn't exist in this particular table
Sounds like you're on to at least part of the problem.  You'll want to spell out the logic for how to uniquely JOIN from resp to elig.  Range of values?  Closest by date?  Maybe you won't be able to do this and need a new elig table that is join-able for use in this query.

Keep in mind that experts here cannot connect to your data source and run queries, so our ability to help you is limited to looking at the T-SQL and any sample data and making educated guesses.
0
Scott PletcherSenior DBACommented:
Use an OUTER APPLY instead of a LEFT OUTER JOIN: review the elig table to see what/if you need to ORDER BY and uncomment it  below as needed:


select distinct resp.patid,
pcd.patient_name_last,
pcd.patient_name_first,
pcd.patient_name_middle,
pcd.date_of_birth,
pcd.patient_ssn,
subs.subs_policy,
resp.plan_cov_desc,
elig.last_name,
case
    when resp.plan_cov_desc = 'Title 19' and subs.guarantor_name = 'Medicaid' then 'Match'
    when resp.plan_cov_desc = 'Mental Health and Substance Abuse' and subs.guarantor_name like '%DMH%' then 'Match'
    else 'Verify'
end as Compare,
subs.guarantor_name,
subs.guarantor_id
from resp
    left outer join pcd on pcd.patid = resp.patid
    left outer join subs on subs.patid = resp.patid
    inner join guar on guar.guarantor_id = subs.guarantor_id
    outer apply (
        select top (1) elig2.last_name
        from elig elig2
        where elig2.file_name = resp.file_name and elig2.segment = 'NM1'
        --order by elig2.{id|date} DESC
    ) as elig        

where resp.file_name = '271SB.rsp'
and resp.plan_cov_desc not in ('DENTAL', 'MAJOR MEDICAL', 'Non Emergency Transportation', 'PHARMACY')
and subs.guarantor_id not like '9%'
and resp.elig_info = '1'
and subs.guarantor_name not in ('Non Recoverable')
and guar.inactive_guar_code = 'N'
order by resp.patid
0

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
fcsITAuthor Commented:
Yeah, that I've got Jim.  I was hoping you guys knew of something with SQL to possibly get around this issue, but it looks like I just need to dig deeper into the tables to try and find something else to join on.

Thanks guys.
0
Scott PletcherSenior DBACommented:
@fcsIT:

Refresh and look at the response just before your last one :-).
0
fcsITAuthor Commented:
No luck Scott.
0
fcsITAuthor Commented:
While I still have the problem, the solution is evident to me that I'm just going to have to keep digging in the database for the right fields to join on.

Thank you to all who responded, it's much appreciated!
0
Scott PletcherSenior DBACommented:
Absolutely, there must be something wrong in the join criteria for at least one other table if the TOP (1) row only on the elig table didn't stop the over-match of rows.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

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.