CODEWITHSUNDEEP
sqlsql-servert-sql
Hisham Amery
Hisham Amery

Reputation: 61

Joining two LEFT JOINS Together

I am trying to combine these two LEFT JOINS together.

Preview of tables I am working with:

For example/context:

Table Name: [GMC Claims 2019]

PatNo RevCode CPT BilledCharges DRG
5 141 null 500 439
5 636 J1234 276000 101

Table Name: BCBS_DRG_REIMBURS

DRG Code Description DRG Weight ALOS High Trim Effective Date DRG Rate Per Diem High Trim Outlier
42 XXXXX YYYYY 30 54 10/1/2018 $235,121.59 $5,486.17
101 XXXXX YYYYY 24 40 10/1/2018 $146,736.72 $4,279.82
439 XXXX YYYYY 13 23 10/1/2018 $63,124.34 $3,399.00

Table Name: [BCBS DRG CARVE OUT 07012016]

DRG SERVICE PMT (SDA)
101 DRG CARVE OUT 13537
439 DRG CARVE OUT 13537

Desired Output:

DRG DRG Reimbursement Carve Out PMT
439 $63,124.34 13537
101 $146,736.72 13537

The logic is, given the claims table as our main table, find the DRG codes that match (101 & 439 in this case) and return the respective values (so DRG Reimbursement from the BCBS_DRG_REIMBURS table and Carve Out PMT from the [BCBS DRG CARVE OUT 07012016] table.

My code/attempt:

SELECT
    [GMC Claims 2019].DRG
FROM
    [GMC Claims 2019]
LEFT JOIN 
    [BCBS DRG CARVE OUT 07012016] ON [GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG
WHERE
    [GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG
LEFT JOIN 
    BCBS_DRG_REIMBURS ON [GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code
WHERE
    [GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code

Upvotes: 1

Views: 78

Answers (2)

eshirvana
eshirvana

Reputation: 24633

when you join , there is condition that you join on, there is no need for where clause , unless there is a reason, however in you case you need inner join not outer join:

SELECT
    *
FROM
    [GMC Claims 2019]
JOIN BCBS_DRG_REIMBURS 
        ON [GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code
JOIN [BCBS DRG CARVE OUT 07012016]  -- < here maybe it should be left join ?
        ON [GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

I think inner join between three tables will server your purpose:

select c.drg, b.Per_Diem_High_Trim_Outlier, d.pmt
from  [GMC Claims 2019] c 
inner join [BCBS_DRG_REIMBURS] b on c.drg = b.drg_code 
inner join [BCBS DRG CARVE OUT 07012016] d on d.drg = b.drg_code

Upvotes: 1

Related Questions