Conditional condition when joining tables in SQL Server

Question

I have two tables both containing employee data, TableA and TableB, I'm joining them based on 2 id's (one it's userID and the other mID (month based id)) using a LEFT OUTER JOIN which returns NULL in about 20% of the results because TableB it's incomplete. I want -if possible- a query that detects if the join doesn't find a match and subtract one month to mID so the JOIN can cover at least a percentage of missing data with just old data.

I don't know if it's a way too complex query but I had in mind something like:

SELECT T1.*, T2.*
FROM TABLEA
LEFT OUTER JOIN TABLEB
ON T2.USERID = T1.USERID AND (CASE WHEN (T2.HID = T1.HID) = NULL THEN (T2.HID = T1.HID-1))

Appreciate any help.

I think you need to provide some sample data and desired output here. — Sean Lange
– Sean Lange, Commented Mar 19, 2019 at 19:07
There are a lot reasons why it is really bad idea to try to deal with keys in this manner. Why is JOINing using 'OLD' data even valid? — David Tansey
– David Tansey, Commented Mar 19, 2019 at 19:27
@DavidTansey, It's likely that TableB contains the same data (95%-ish usually) as one month earlier, but I'm not able to update the table myself, besides, that table just contains reference data about the same employees of tableA — Rayén
– Rayén, Commented Mar 19, 2019 at 19:34

UnhandledExcepSean · Accepted Answer · 2019-03-19 20:27:35Z

I think this is what you are really wanting to do; the downside is there is a bit more logic and it would be duplicated per column, but it provides a fine grain of control.

DECLARE @TableA TABLE (USERID INT,HID INT,SomeData VARCHAR(20))
DECLARE @TableB TABLE (USERID INT,HID INT,SomeData VARCHAR(20))

INSERT INTO @TableA(USERID,HID,SomeData) SELECT 1,5,'Now'
INSERT INTO @TableA(USERID,HID,SomeData) SELECT 2,5,NULL
INSERT INTO @TableA(USERID,HID,SomeData) SELECT 3,5,NULL

INSERT INTO @Tableb(USERID,HID,SomeData) SELECT 2,4,'Now-1'
INSERT INTO @Tableb(USERID,HID,SomeData) SELECT 2,3,'Now-2'
INSERT INTO @Tableb(USERID,HID,SomeData) SELECT 3,4,'Now-1'

SELECT
    t1.USERID, T1.Hid AS [Current HID]
    ,
        CASE
            WHEN T1.SomeData IS NOT NULL THEN T1.SomeData
            WHEN T2.USERID IS NOT NULL THEN T2.SomeData
            WHEN T3.USERID IS NOT NULL THEN T3.SomeData
            ELSE T1.SomeData
        END AS [Most Recent SomeData]
FROM @TABLEA T1
LEFT JOIN @TABLEB T2 ON T2.USERID = T1.USERID AND T2.HID = T1.HID
LEFT JOIN @TABLEB T3 ON T3.USERID = T1.USERID AND T3.HID = T1.HID-1

PPJN · Accepted Answer · 2019-03-20 02:21:10Z

1

You were on the right track by using a case statement but it was just a little out of order. Warning! I didn't test it but I believe I ran into something similar in the past.

SELECT      T1.*
            ,T2.*
FROM        TABLEA  t1
LEFT JOIN   TABLEB  t2  ON      T2.USERID = T1.USERID
                            AND T2.HID =    CASE
                                                WHEN T2.HID = T1.HID then t1.hid
                                                else T1.HID - 1
                                            end

answered Mar 20, 2019 at 2:21

PPJN

3643 silver badges14 bronze badges

3 Comments

Rayén Over a year ago

Your solution worked reducing NULLS but allowed duplicates so I got double the results.

PPJN Over a year ago

Would adding distinct solve the dupes? Without test data, I can’t tell.

Rayén Over a year ago

Yes I just did! Switched the LEFT JOIN for a INNER but I still need to manually check data.

Collectives™ on Stack Overflow

Conditional condition when joining tables in SQL Server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related