CODEWITHSUNDEEP
sqlsql-serverjoin
Rahul
Rahul

Reputation: 903

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.

Upvotes: 0

Views: 53

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The final column is easy . . . left join:

select b.a, b.b, coalesce(a.d, 0) as d
from b left join
     a
     on b.a = a.b and b.b = a.c;

The first column doesn't really make sense but perhaps:

select max(a.a) over () as a,
       b.a as b, b.b as c, coalesce(a.d, 0) as d
from b left join
     a
     on b.a = a.b and b.b = a.c;

Upvotes: 0

Related Questions