How to apply same COALESCE argument across a number of columns

Question

I have to join two tables on two conditions. I want that if the second condition doesn't hold but there is a blank cell in Table 1 then to join to that row. If the second condition doesn't hold then to return a zero.

Join Table 1 and Table 2 - return Table 2 and column 3, 4 and 5 from Table 1.

DECLARE @table1 TABLE (
     letter CHAR(1),
     num1 INT,
     num2 NUMERIC(5,2),
     num3 INT,
     num4 NUMERIC(5,2)
)  

DECLARE @table2 TABLE (
    letter CHAR(1),
    num1 INT
)
INSERT INTO @table1 VALUES      
    ('A', 1, 0.25, 10, 0.5),  
    ('A', 2, 0.50, 15, 0.75),  
    ('A', 3, 0.25, 20, 1),  
    ('A', null, 0.50, 25, 1.5),  
    ('B', 1, 0.25, 10, 0.5),  
    ('B', 2, 0.50, 15, 0.5),  
    ('B', 3, 0.25, 20, 0.75)

INSERT INTO @table2 VALUES   
    ('A', 1),  
    ('A', 2),  
    ('A', 3),  
    ('A', 5),  
    ('B', 1),  
    ('B', 2),  
    ('B', 3),  
    ('B', 5)   

Something like:

SELECT t2.*,     
    COALESCE(  
        (SELECT TOP 1 num2, num3, num4 FROM @table1 WHERE letter = t2.letter AND num1 = t2.num1),   
        (SELECT TOP 1 num2, num3, num4 FROM @table1 WHERE letter = t2.letter AND num1 IS NULL),   
        0  
    ) AS missing_number  
FROM @table2 t2

Desired Output:

(A, 1, 0.25, 10, 0.5),  
(A, 2, 0.50, 15, 0.75),  
(A, 3, 0.25, 20, 1),  
(A, 5, 0.50, 25, 1.5),  
(B, 1, 0.25, 10, 0.5),  
(B, 2, 0.50, 15, 0.5),  
(B, 3, 0.25, 20, 0.75),  
(B, 5, 0.00, 0, 0.00)  

Answer 1

SELECT t2.*, 
    COALESCE(t1.num2, t3.num2, 0) AS num2, 
    COALESCE(t1.num3, t3.num3, 0) AS num3, 
    COALESCE(t1.num4, t3.num4, 0) AS num4
FROM @table2 t2
LEFT OUTER JOIN @table1 t1 ON t2.letter = t1.letter AND t2.num1 = t1.num1
LEFT OUTER JOIN @table1 t3 ON t2.letter = t3.letter AND t3.num1 IS NULL

In SQL, there is no way to know if current row has one match or not during table join. We can't express joining @table2 to a nullable special row (where @table1.num1 is null) from @table1 when rows from @table2 don't have a match by some column. Multiple joins are required. The second join is based on a normal join by column letter and num1. Try below query would give expected structure.

SELECT *
FROM @table2 t2
LEFT OUTER JOIN @table1 t1 ON t2.letter = t1.letter AND t2.num1 = t1.num1

Then match the special row to every rows in @table2.

Answer 2

TRY THIS:

SELECT tt.letter,
    tt.num1,
    COALESCE(tt.num2, t1.num2, 0.00) AS num2,
    COALESCE(tt.num3, t1.num3, 0) AS num3,
    COALESCE(tt.num4, t1.num4, 0.00) AS num4
FROM(
    SELECT t2.letter,
        t2.num1,
        t1.num2,
        t1.num3,
        t1.num4
    FROM @table2 t2
    LEFT JOIN @table1 t1 ON t2.letter = t1.letter
        AND t2.num1 = t1.num1) tt
    LEFT JOIN @table1 t1 ON t1.letter = tt.letter
        AND t1.num1 IS NULL

OUTPUT:

letter  num1    num2    num3    num4
A       1       0.25    10      0.50
A       2       0.50    15      0.75
A       3       0.25    20      1.00
A       5       0.50    25      1.50
B       1       0.25    10      0.50
B       2       0.50    15      0.50
B       3       0.25    20      0.75
B       5       0.00    0       0.00