Search code examples
sql-serverjoinunion

SQL Server : UNION with fill NULL value from one of the table columns

I have two SQL Server tables Table1 and Table2 defined like this:

Table1:

Well_id completion_id Well_Name Api_No10
111 221 Well1_Comp1 Well1
112 222 Well2_Comp1 Well2
113 223 Well3_Comp1 Well3

Table2:

Well_id completion_id Well_Name Api_No10
111 Well1_Comp2
114 Well4_Comp1
115 Well5_Comp1

Table1 is the main table and Table2 rows are being added to Table1 based on a primary index criteria.

I am able to apply UNION to combine the rows from two tables based on a 'defined' primary_index column as:

WITH Comp_View AS
(
    SELECT
        Well_id, Completion_id, Well_Name, Api_No10,
        CONCAT(Well_Id, '_', Well_Name) AS Primary_Index
    FROM
        table1
),
Pipeline_report AS
(
    SELECT
        Well_id, Completion_id, Well_Name, Api_No10,
        CONCAT(Well_Id,'_',Well_Name) AS Primary_Index
    FROM
        table2
)
SELECT comp.*               
FROM Comp_View comp
UNION
SELECT pipe.*               
FROM Pipeline_report pipe
WHERE Primary_index NOT IN (SELECT Primary_Index FROM Comp_view)

I get these results:

enter image description here

The table2 has null values in the Api_no10 column. I would like to populate the Api_no10 column in the final table if the 'Wellid' from table2 exists in table1 as well.

In the result set, the APi_no10 column for the well_id: 111 with Primary_index: 111_well1_comp2 should be 'Well1' but it's NULL based on my query.

Solution

  • You can add a left join to the second half of your union.

    drop table if exists #Table1, #Table2;
create table #Table1 (Well_id int, completion_id int, Well_Name nvarchar(30), Api_No10 nvarchar(30));
insert into #Table1 (Well_id, completion_id, Well_Name, Api_No10) 
values
    (111,221,'Well1_Comp1','Well1'),
    (112,222,'Well2_Comp1','Well2'),
    (113,223,'Well3_Comp1','Well3');
create table #Table2 (Well_id int, completion_id int, Well_Name nvarchar(30), Api_No10 nvarchar(30));
insert into #Table2 (Well_id, completion_id, Well_Name, Api_No10) 
values
(111,null,'Well1_Comp1',null),
(114,null,'Well4_Comp1',null),
(115,null,'Well5_Comp1',null)

;with Comp_View as
(
    select
        Well_id,
        completion_id,
        Well_Name,
        Api_No10,
        concat(Well_Id,'_',Well_Name) as Primary_Index
    from
        #Table1
),
Pipeline_report as
(
    select
        Well_id,
        completion_id, 
        Well_Name,
        Api_No10,
        CONCAT(Well_Id,'_',Well_Name) as Primary_Index
    from
        #Table2
)

select comp.*               
from Comp_View comp
union
select 
    pipe.Well_id,
    pipe.completion_id,
    pipe.Well_Name,
    isnull(pipe.Api_No10, comp.Api_No10), -- // Gets you the Api_No10 if it available from Table1 but is not in Table2
    pipe.Primary_Index
from 
    Pipeline_report pipe
    left join Comp_View comp
    on pipe.Well_id = comp.Well_id
    and pipe.Well_Name = comp.Well_Name;

    Results:

    Well_id completion_id Well_Name Api_No10 Primary_Index
    111 NULL Well1_Comp1 Well1 111_Well1_Comp1
    111 221 Well1_Comp1 Well1 111_Well1_Comp1
    112 222 Well2_Comp1 Well2 112_Well2_Comp1
    113 223 Well3_Comp1 Well3 113_Well3_Comp1
    114 NULL Well4_Comp1 NULL 114_Well4_Comp1
    115 NULL Well5_Comp1 NULL 115_Well5_Comp1