asked on

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

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arun Murugan

which means the data in the elig table is duplicate. Can you post the tables structure?

fcsIT

ASKER

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.

Jim Horn

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fcsIT

ASKER

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.

Scott Pletcher

@fcsIT:

Refresh and look at the response just before your last one :-).

fcsIT

ASKER

No luck Scott.

fcsIT

ASKER

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!

Scott Pletcher

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.