CODEWITHSUNDEEP
sqlpostgresqlquerying
Javier
Javier

Reputation: 49

Select everything from one table and some from anothe table on SQL joining on 2 variables

I am having a tough time selecting data from two tables. I tried using every join but cannot figure it out. I am using Postgresql

These are my tables: Table_a

date, class, count_of_a
4/1/2015, B, 888
4/2/2015, A, 533
4/2/2015, A, 432
4/3/2015, C, 484

Table_b

date, class, count_of_b
4/2/2015, B, 345
4/3/2015, D, 553
4/3/2015, C, 334

I want this as my result:

date, class, count_of_a, count_of_b
4/2/2015, B,    , 345
4/3/2015, D,    , 553
4/1/2015, B, 888,
4/2/2015, A, 533,
4/2/2015, A, 432,
4/3/2015, C, 484, 334

Upvotes: 1

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this with either a full outer join or union all and group by:

select date, class, sum(count_of_a) as count_of_a, sum(count_of_b) as count_of_b
from ((select date, class, count_of_a, NULL as count_of_b
       from table_a
      ) union all
      (select date, class, NULL as count_of_a, count_of_b
       from table_b
      )
     ) ab
group by date, class;

This method has a couple of advantages over full outer join (apart from not needed a bunch of coalesce() statements in the from). Primarily, it is more general, so it will work when date and/or class take on NULL values. Also, it will add the values correctly when there are multiple values for a class/date combination.

Upvotes: 1

Related Questions