CODEWITHSUNDEEP
sqljoin
Sotark
Sotark

Reputation: 196

How to join tables selecting both matched and unmatched records?

I have tables that do a count of a certain action for different days. I am trying to join those tables to show each type of action and the count for each day. If the action happened on both days its fine. But if I have an action that only happened on day one or only on day two, I would like those as well.

I have tried different types of joins and unions and can not seem to get it to show the expect results.

In table 1 and 2 I have first four columns that match based on the pl & sta(the two fields I want to join on) so I should get the count from both tables as shown in the outcome. However table 1 has one that is 414 - 1PL with a count of 26 and table 2 has 41M - 2PL with a count of 12. I would like each of those that are unmatching returned in the pl and sta fields with the count of each one under that column for that days count.

Table data:

Table 1
pl  | sta | count_1
----+-----+--------
410 | 1PL |    7777
410 | MPL |      31
412 | 1PL |      67
412 | 3PL |       1
414 | 1PL |      26

Table 2
pl  | sta | count_2
----+-----+--------
410 | 1PL |    7000
410 | MPL |      26
412 | 1PL |      52
412 | 3PL |       1
41M | 2PL |      12

Outcome
pl  | sta | count_1 | count_2
----+-----+---------+--------
410 | 1PL |    7777 |    7000
410 | MPL |      31 |      26
412 | 1PL |      67 |      52
412 | 3PL |       1 |       1
414 | 1PL |      26 |       0
41M | 2PL |       0 |      12

Upvotes: 0

Views: 1397

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

In standard SQL, you can write this using a full join. In doing so, I recommend the using clause:

select pl, sta,
       coalesce(t1.count_1, 0) as count_1,
       coalesce(t2.count_2, 0) as count_2
from t1 full join
     t2
     on t1.pl = t2.pl and t1.sta = t2.sta;

Most, but not all databases, support using.

You can also write this as a union all/group by:

select pl, sta, sum(count_1) as count_1, sum(count_2) as count_2
from ((select pl, sta, count_1, 0 as count_2
       from t1
      ) union all
      (select pl, sta, 0, count_2
       from t2
      )
     ) t2
group by pl, sta;

This is useful under some circumstances:

  • If you have multiple rows per pl/sta in either table.
  • If either pl or sta could ever be NULL.
  • If you have an aversion to using COALESCE(). (That is a joke).

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

I think you need a full join and coalesce:

SELECT COALESCE(t1.pl, t2.pl) AS pl
    , COALESCE(t1.sta, t2.sta) AS sta
    , COALESCE(t1.count_1, 0) AS count_1
    , COALESCE(t2.count_2, 0) AS count_2
FROM t1
FULL JOIN t2 ON t1.pl = t2.pl AND COALESCE(t1.sta, -99) = COALESCE(t2.sta, -99)

In order to account for cases where sta is NULL, replace sta with -99 or some other value that is otherwise invalid.

Upvotes: 2

rajesh vaishnav
rajesh vaishnav

Reputation: 31

You can try FULL OUTER JOIN example :

SELECT * FROM tableA FULL OUTER JOIN tableB ON tableA.pl =tableB.pl

Upvotes: 0

Related Questions