SQL Join on weird dataset-CodePudding

I have a request to join two tables. Here is a script you can run for sample data representative of my real scenario.

IF OBJECT_ID('tempdb..#Table_A') IS NOT NULL DROP TABLE #Table_A
CREATE TABLE #Table_A (
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 CHAR(1) NOT NULL
)
INSERT INTO #Table_A (Col1, Col2, Col3) VALUES
(1011,1017,'Y'),
(1247,1834,'N')

IF OBJECT_ID('tempdb..#Table_B') IS NOT NULL DROP TABLE #Table_B
CREATE TABLE #Table_B (
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 CHAR(1) NOT NULL,
    Col4 INT,
    PRIMARY KEY (Col1,Col2,Col3)
)
INSERT INTO #Table_B (Col1, Col2, Col3, Col4) VALUES
(1011,1017,'N',63),
(1247,1834,'N',850),
(1247,1834,'Y',984)

SELECT * FROM #Table_A
SELECT * FROM #Table_B

The top table is Table_A and the bottom table is Table_B.

SELECT
a.Col1,a.Col2,a.Col3,b.Col4
FROM #Table_A a
LEFT OUTER JOIN #Table_B b
ON a.Col1 = a.Col1
AND a.Col2 = b.Col2
AND a.Col3 = b.Col3

Result:

In the above join, row 1 has NULL for Col4. That's because Col3 didn't match. But the request by the business is that if Col1 and Col2 match but Col3 does not match, still return the Col4 value.

Desired: First, look for a match on all 3 columns. But, if you only get a match on Col1 and Col2, then ignore the match on Col3. So, in the above join, Col4 on row 1 should have 63. This will not result in multiplying out the rows in Table_A because Col3 values are Y/N.

How would you write this in SQL?

CodePudding user response：

You may join table B another time, if there is no matches in a first join, like:

SELECT
    a.Col1,
    a.Col2,
    a.Col3,
    COALESCE(b.Col4, b_back_up.Col4)
FROM #Table_A a
LEFT OUTER JOIN #Table_B b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    AND a.Col3 = b.Col3
LEFT OUTER JOIN #Table_B b_back_up
    ON a.Col1 = b_back_up.Col1
    AND a.Col2 = b_back_up.Col2
    AND b.Col3 IS NULL

CodePudding user response：

This solution should work.

SELECT
a.Col1,a.Col2,a.Col3,IIF(b.Col4 IS NOT NULL, b.Col4, b2.Col4)
FROM #Table_A a
LEFT OUTER JOIN #Table_B b
ON a.Col1 = a.Col1
AND a.Col2 = b.Col2
AND a.Col3 = b.Col3
LEFT JOIN #Table_B b2
on a.Col1 = b2.Col1
AND a.Col2 = b2.Col2
AND b.Col4 IS NULL

Basicly, you should do another join on table2 but just where first time you got NULL value in Col4