SQL Join on weird dataset

Question

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?

If Col3 has no effect on the joined result quantity, nor it is possible that you have in any of the tables 2 entries with the same Col1 and Col2 values, but different Col3, why Col3 is in the join condition? Are your results correct if you just leave out Col3? — Pred, Aug 03 '22 at 06:36
Please confirm or correct in the question: a.Col1 = a.Col1 OR a.Col1 = b.Col1? — VBoka, Aug 03 '22 at 06:39
This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly" or "it is false that". When you write "Hopefully, you will see what I am saying" it's time to edit your post for clarity. Etc etc. — philipxy, Aug 03 '22 at 06:50
Re the final image: [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [mre] (And given your fine tabular initialization code the input images are redundnant.) — philipxy, Aug 03 '22 at 06:56
It's really good you gave the start state & a relevant part you could do & most of a [mre]. But it's really not clear what the desired ultimate output is as a function of input. It would also help if you explain how you are stuck. (While not giving wrong code.) Really important: Again: "When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values." (And repeating code in prose is not helpful.) PS [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) — philipxy, Aug 03 '22 at 07:02
SELECT + CASE. PS Each JOIN ON does a CROSS JOIN & of the row pairings then keeps ON ones, then we keep the WHERE ones. Then for each remaining row, what do you want to output as a function of it? — philipxy, Aug 03 '22 at 07:17

score 2 · Answer 1 · answered Aug 03 '22 at 06:48

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

score 2 · Answer 2 · answered Aug 03 '22 at 06:49

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

SQL Join on weird dataset

2 Answers2