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