CODEWITHSUNDEEP
sqlsql-serverselectjoin
Spencer
Spencer

Reputation: 317

MSSQL-How to join a table and for those null values, specifically use one item from the joined tables?

Please check the tables below:

When table 1 left join Table 2 on Column ItemName, for Pear and Orange, the Coef column will be null. How can I get those non mapped items to all match for ItemName = Others in Table 2?

Currently, I am using temp table to filter those null items , put the correct coef and then union with the mapped item results to get the final Table 3.

Are there any better ways to achieve this?

Table 1:

ItemName  |  Cost  |
Apple     |  1     |
Banana    |  2     |
Pear      |  3     |
Orange    |  4     |

Table 2:

ItemName  |  Coef  |
Apple     |  0.1   |
Banana    |  0.2   |
Others    |  0.8   |

Expected Results:

ItemName  |  Cost  |  Coef  |
Apple     |  1     |  0.1   |
Banana    |  2     |  0.2   |
Pear      |  3     |  0.8   |
Orange    |  4     |  0.8   |

Upvotes: 1

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use two left joins:

select t1.*,
       coalesce(t2.coef, t2o.coef) as coef
from t1 left join
     t2
     on t1.itemname = t2.itemname left join
     t2 t2o
     on t2o.itemname = 'Others';

Upvotes: 3

Mureinik
Mureinik

Reputation: 311998

You could cross join your result with that single row and take the first non-null value:

SELECT     t1.itemname, t1.cost, COALESCE(t2.coef, def.coef)
FROM       t1
LEFT JOIN  t2 ON t1.itemname = t2.itemname
CROSS JOIN (SELECT coef
            FROM   t2
            WHERE  itemname = 'Others') def

Upvotes: 3

Related Questions