CODEWITHSUNDEEP
sqlsql-serversql-server-2012
MCP_infiltrator
MCP_infiltrator

Reputation: 4179

Left Join not matching where data exists - Temp Tabls SQL 2012

I have a query that I am using to pull in data from different tables. I have broken downthe query into a few different parts to make like a little easier. The problem is that when an ID number exists for sure in both places the LEFT JOIN does not always match them up, somtimes it does sometimes it does not.

All of the ID numbers are of type INT so I know all the data types are the same. I tried to use COLLATE SQL_Latin_General_Pref_CP1_CI_AS in the join statement but that is invalid for data of type INT was the error I received. OK fine, but I am really stuck as I don't know where to proceede from here other than dumping into excel and doing a vlookup.

Here is what I have:

DECLARE @sd DATETIME;
DECLARE @ed DATETIME;

SET @sd = '2014-01-01';
SET @ed = '2015-10-01';

DECLARE @denials_write_offs TABLE (
    pk INT IDENTITY(1, 1) PRIMARY KEY
    , pt_id               INT
    , bill_no             INT
    , denials             FLOAT
)
INSERT INTO @denials_write_offs
SELECT a.pt_id
, a.bill_no
, a.denials_woffs

FROM (
    SELECT CAST(pt_id AS INT) pt_id
    , CAST(bill_no AS INT) bill_no
    , SUM(tot_pay_adj_amt) AS denials_woffs

    FROM smsmir.mir_pay
    JOIN smsdss.c_Softmed_Denials_Detail_v 
    ON smsmir.mir_pay.pt_id = smsdss.c_Softmed_Denials_Detail_v.bill_no

    WHERE discharged >= @sd
    AND discharged < @ed
    AND LEFT(smsmir.mir_pay.pay_cd, 4) = '0974'

    GROUP BY pt_id
    , bill_no
) A

--------------------------------------

DECLARE @EDTBL TABLE (
    ACCOUNT INT
    , ED_MD VARCHAR(MAX)
)

INSERT INTO @EDTBL
SELECT Z.*
FROM (
    SELECT CAST(ACCOUNT AS INT) ACCOUNT
    , ED_MD

    FROM SMSDSS.c_Wellsoft_Rpt_tbl
) Z

----------------------------------------

DECLARE @TmpDenialsTbl TABLE (
PK INT IDENTITY(1, 1)         PRIMARY KEY
, BILL_NO                     INT
     ...
     ...
     ...
 )

INSERT INTO @TmpDenialsTbl
SELECT *
FROM (
SELECT bill_no
...
...
...
)

So as you can see, from the above every pt_id or bill_no is put into a table as an INT but I cannot for the life of me figure out why sometimes I get a match on my LEFT JOIN and why sometimes I do not. I did pull data from the @EDMD table and get the account number I was looking for but it did not hit on the left join below:

FROM @TmpDenialsTbl                       A
LEFT OUTER JOIN @denials_write_offs       D
ON A.bill_no = d.pt_id
LEFT OUTER JOIN @EDTBL                    C
ON C.Account = D.bill_no
LEFT OUTER JOIN @USERTBL                  F
ON A.CERM_RVWR_ID = F.login_id
    AND F.RN = 1

Example of what I am doing and what I get back

DECLARE @TmpDenials TABLE (
    PT_ID INT
)

INSERT INTO @TmpDenials
SELECT A.*
FROM (
    SELECT CAST(PT_ID AS INT) PT_ID
    FROM SOME_TABLE
) A

DECLARE @EDMD TABLE (
    PT_ID INT
    EDMD VARCHAR(MAX)
)

INSERT INTO @EDMD
SELECT B.*
FROM (
    SELECT CAST(PT_ID AS INT) PT_ID
    EDMD

    FROM SOME_OTHER_TABLE
)B

SELECT * FROM @TmpDenials
PT_ID     |
123456789 |

SELECT * FROM @EDMD
PT_ID     | ED_MD
123456789 | Dr. Emergency Room

SELECT *
FROM @TmpDenials      A
LEFT OUTER JOIN @EDMD B
ON A.PT_ID = B.PT_ID

A.PT_ID    | B.PT_ID    | ED MD
123456789  | NULL       | NULL

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I'm not sure if this is the cause of your problem or not, but this condition is potentially suspicious:

FROM @TmpDenialsTbl A LEFT OUTER JOIN
     @denials_write_offs D
     ON A.bill_no = d.pt_id LEFT OUTER JOIN
     @EDTBL C
     ON C.Account = D.bill_no LEFT OUTER JOIN
--------^
     @USERTBL F
     ON A.CERM_RVWR_ID = F.login_id AND F.RN = 1

If there is no match to the C table form A, then this will always fail to match. I'm not sure if this is the intended behavior.

The other option is that a WHERE clause is filtering out the rows that you want.

Note: When you use table aliases, you should use abbreviations for the table names, so i, wo, e, and u are better than the arbitrary letters a, d, c, and f.

Upvotes: 1

Related Questions