Adding missing records from another columns in a different table

Question

I have 2 tables :

Table A:

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

Table B :

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.

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-12 19:44:47Z

1

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

edited Mar 12, 2019 at 19:44

answered Mar 12, 2019 at 19:26

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rahul Over a year ago

Cross join gives the same issue as the full outer join. Gives more records as it is a cartesian product.

Zaynul Abadin Tuhin Over a year ago

@Rahul check the eidted answer please it is showing your expected output

Rahul Over a year ago

Please take the Column D into account as well

Rahul Over a year ago

Yes sir. I got the last join after the distinct got me the required result! thanks!! :)

Zaynul Abadin Tuhin Over a year ago

Let us continue this discussion in chat.

Gordon Linoff · Accepted Answer · 2019-03-12 19:33:50Z

0

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;

answered Mar 12, 2019 at 19:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Rahul Over a year ago

Still only gives me the 3 columns

Gordon Linoff Over a year ago

@Rahul . . . The second query is selecting four columns, not three.

Rahul Over a year ago

I meant "gives me 3 records". Sorry

Gordon Linoff Over a year ago

@Rahul . . . b has twelve records. Nothing is being filtered. The join is an outer join. This should return at least 12 records.

Collectives™ on Stack Overflow

Adding missing records from another columns in a different table

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related