Skip to content
Advertisement

Adding missing records from another columns in a different table

I have 2 tables :

Table A:

    A | B | C   |  D
   qwe  4  2019   10
   qwe  2  2020   5
   qwe  5  2019   5

Table B :

A | B 
3  2019
4  2019
5  2019
6  2019
7  2019
8  2019
9  2019
10 2019
11 2019
12 2019
1  2020
2  2020

I want the output table to contain all numbers and years from table B in Table A. So, the text qwe should be shown 12 times with respective numbers.

Output Table:

A  | B | C  | D
qwe  3  2019  0
qwe  4  2019  10
qwe  5  2019  5
qwe  6  2019  0
qwe  7  2019  0
qwe  8  2019  0
qwe  9  2019  0
qwe 10 2019   0
qwe 11 2019   0
qwe 12 2019   0
qwe 1  2020   0
qwe 2  2020  5

Note: Table A contains more values than qwe so i am trying to replicate 12 values for all of the different records. Column D should be showing 0 for records that dont exist.

I have tried a full outer join but a full outer join produces 12 records for each record of qwe in table A.

Advertisement

Answer

what about cross join

     with cte as
(
select 'qwe' as A, 4 as B, 2019 as C, 10 as D
union all
select 'qwe',2,2020,5
union all
select 'qwe',5,2019,5
),
cte1 as
(
select 3 as A, 2019 as B union all
select 4 , 2019 union all
select 5 , 2019 union all
select 6,  2019 union all
select 7,  2019 union all
select 8,  2019 union all
select 9,  2019 union all
select 10,  2019 union all
select 11,  2019 union all
select 12,  2019 union all
select 1,  2020 union all
select 2,  2020 
)
  , cte3 as
  (select distinct a.A,b.A  as B,b.B as c 
   from cte1 b cross join (select A from cte) a
   ) select distinct t1.*,coalesce(t2.D,0) as D from cte3 t1 left join cte t2 
    on t1.A=t2.A and t1.C=t2.C and t1.B=t2.B

output

 A  B    c      D
qwe 3   2019    0
qwe 4   2019    10
qwe 5   2019    5
qwe 6   2019    0
qwe 7   2019    0
qwe 8   2019    0
qwe 9   2019    0
qwe 10  2019    0
qwe 11  2019    0
qwe 12  2019    0
qwe 1   2020    0
qwe 2   2020    5
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement