CODEWITHSUNDEEP
t-sqljoinnull
Johann
Johann

Reputation: 29867

T-SQL: Selecting column from an alternate table when null

I have the following table, TableA, with data:

ID    ColA
0     10
1     null
2     20

I have another table, TableB, with the following data.

ID    ColB   ColC
1     30     80
1     40     70 
3     50     100

I need to select rows in TableA but when ColA in the row is null, I want to retrieve the value of ColB in TableB (if one exists) and use it in place of ColA. If no value in ColB exists, then the value of ColA in the result should be null. The join is done on TableA.ID and TableB.ID. TableB can have multiple rows where the ID column repeats. TableB.ID and TableB.ColC together make a row unique. So my result should look like this if ColC is limited to the value of 70:

ID   ColA
0     10 
1     40
2     20

Not sure how to do this. Thanks for your help!

Upvotes: 2

Views: 1243

Answers (3)

aF.
aF.

Reputation: 66697

select a.ID, COALESCE(a.ColA, b.ColB) as 'ColA'
from TableA a
left join TableB b on a.ID = b.ID and b.ColC = 70

Upvotes: 1

David M
David M

Reputation: 72860

This seems to do what you want if I have correctly interpreted your question:

SELECT  a.ID,
        ISNULL(a.ColA, b.ColB) ColA
FROM    TableA a
        LEFT JOIN
                TableB b
                ON a.ID = b.ID
                AND b.ColC = 70

I have literally "limited to the value of 70" in ColC as you stated.

Upvotes: 1

madeFromCode
madeFromCode

Reputation: 721

sounds like you're looking for a case statement. try case when TableA.Value is null then TableB.Value end

SQL Case Statements

Upvotes: 0

Related Questions