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