CODEWITHSUNDEEP
sqlsql-servert-sqljoincase
Rayén
Rayén

Reputation: 59

Conditional condition when joining tables in SQL Server

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.

Upvotes: 2

Views: 107

Answers (2)

PPJN
PPJN

Reputation: 352

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

Upvotes: 1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

Upvotes: 1

Related Questions