SQl left outer join to fill null

Question

I have joined two tables in SQL (see below) using a left outer join.

Is it possible to fill the null cells with other data?

(from fiddle)

CREATE TABLE Data1
    (cost_type nvarchar(10), contract nvarchar(10))
;

INSERT INTO Data1
    (cost_type, contract)
VALUES
    ('LABR', 'contract1'),
    ('EQP', ''),
    ('RST', 'contract1')
;

CREATE TABLE data2
    (cost_type nvarchar(10), contract nvarchar(10), Name nvarchar(max))
;

INSERT INTO data2
    (cost_type,contract,Name)
VALUES
    ('LABR','contract1','John'),
    ('MAT','contract1','John'),
    ('EQP','','')
;

Query currently being run:

select * from data1 dt1 
left outer join data2 dt2 on dt1.cost_type=dt2.cost_type

The result I need is the following:

COST_TYPE  CONTRACT  NAME  
LABR, contract1, John  
EQP, contract1, John  
RST, contract1, John

This result is based on finding the most used value for the CONTRACT column and replacing all of the nulls with that value. I need to do the same thing for the NAME column.

You would need to join the table once more, and join by the previous row — Kermit
You people should not downvote on any question asked by new users. By doing this, you are discouraging new users. — Vivek Sadh
Initially i also thought of a downvote but after seeing his fiddle i was impressed and his question was understandable. — Vivek Sadh
@rockwell Since you're not ordering by anything, the order of the rows is random. If EQP would happen to end up on top, what value would you want? From another random row? — Joachim Isaksson

Joachim Isaksson Joachim Isaksson · Accepted Answer · 2013-05-22T18:00:57

EDIT: When you made it clear in the comments above that you wanted the values filled with the most used contracts/names, the query wasn't too complex to write; a CTE to find each the most usual contract/name, and a query that replaces empty values with the CTE values;

WITH most_used_contract AS (
  SELECT TOP 1 contract FROM Data1
  WHERE contract IS NOT NULL
  GROUP BY contract
  ORDER BY COUNT(*) DESC
), most_used_name AS (
  SELECT TOP 1 name FROM Data2
  WHERE name IS NOT NULL
  GROUP BY name
  ORDER BY COUNT(*) DESC
)
SELECT data1.cost_type, 
       CASE WHEN data1.contract IS NULL OR data1.contract = ''
            THEN muc.contract
            ELSE data1.contract END contract, 
       CASE WHEN data2.name IS NULL OR data2.name = ''
            THEN mun.name
            ELSE data2.name END name
FROM data1  
LEFT JOIN data2 ON data1.cost_type=data2.cost_type
LEFT JOIN most_used_contract muc ON 1=1
LEFT JOIN most_used_name mun ON 1=1

An SQLfiddle to test with.

SQl left outer join to fill null

1 Answers