CODEWITHSUNDEEP
sqlsql-server
ZMannion
ZMannion

Reputation: 199

How to apply same COALESCE argument across a number of columns

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)

Upvotes: 6

Views: 1388

Answers (4)

onedaywhen
onedaywhen

Reputation: 57093

The approach I'd take is to code each 'rule' as a query in its own right, then UNION the three results together. I personally find such an approach not only easier to code in the first instance but to later maintain:

SELECT t1.letter, t1.num1, t1.num2, t1.num3, t1.num4
  FROM table1 t1
 WHERE EXISTS ( SELECT *
                  FROM table2 t2
                 WHERE t2.letter = t1.letter
                   AND t2.num1 = t1.num1 )
UNION
SELECT t2.letter, t2.num1, t1.num2, t1.num3, t1.num4
  FROM table1 t1, table2 t2
 WHERE t2.letter = t1.letter
       AND t1.num1 IS NULL
       AND NOT EXISTS ( SELECT * 
                          FROM table1 
                         WHERE letter = t2.letter
                               AND num1 = t2.num1 )
UNION
SELECT t2.letter, t2.num1, 0 AS num2, 0 AS num3, 0 AS num4
  FROM table1 t1, table2 t2
 WHERE t2.letter = t1.letter
       AND NOT EXISTS ( SELECT * 
                          FROM table1 
                         WHERE letter = t2.letter
                               AND num1 = t2.num1 )
       AND NOT EXISTS ( SELECT * 
                          FROM table1 
                         WHERE letter = t2.letter
                               AND num1 IS NULL );

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 2

Mazhar
Mazhar

Reputation: 3837

This I think works

SELECT DISTINCT
     T2.letter
    ,T2.num1
    ,num2 = COALESCE(T1.num2, t11.num2,0)
    ,num3 = COALESCE(T1.num3, t11.num3, 0)
    ,num4 = COALESCE(T1.num4, t11.num4,0)
FROM @table2 T2
LEFT JOIN @table1 T1 ON t1.letter = t2.letter AND T1.num1 = T2.num1
LEFT JOIN(
    SELECT * FROM @table1 TA1 WHERE TA1.num1 IS NULL
) T11 ON t11.letter = t2.letter

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  --Null rows
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  --Null rows

Upvotes: 1

qxg
qxg

Reputation: 7036

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.

Upvotes: 2

Related Questions