SQL joining 2 tables with some overlapping values

Question

I am trying to create one table out of the following 2 tables and running into a lot of problems.

Table 1 has the following:

Month,
Salesperson_Assigned,
Product1_Revenue

Table 2 has the following (different product):

Month,
Salesperson_Assigned,
Product2_Revenue

The problem is that while all the months are the same, there are some salespeople in Table 1 not in Table 2, and vice versa. Obviously the revenue is different. I want to join the tables to look like Month, Salesperson, Product1_revenue + Product2_revenue.

Here's the query I'm using:

SELECT
Table1.month,
Table1.salesperson_assigned,
sum(Table1.revenue + Table2.revenue)

FROM
Table1
INNER JOIN Table 2 ON (Table1.month = Table2.month) AND (Table1.salesperson_assigned = Table2.salesperson_assigned)

The output isn't correct even though I confirmed the revenue values in each individual table are correct. The output seems to be making up additional values for the salespeople who appear in only 1 table for the field that should be null.

Anyone had any advice for how to join the 2 tables properly so it acts similar to a pivot table, adding the unique values to the "salesperson assigned" column and adding the revenue for both columns, but when a salesperson is in only ONE table having it respect that there is a zero value for revenue?

score 1 · Answer 1 · answered Sep 29 '13 at 11:59

did you tried this way:

 SELECT
    Table1.month,
    Table1.salesperson_assigned,
    sum(Table1.revenue + Table2.revenue)

    FROM
    Table1 FULL OUTER JOIN Table2 on (Table1.month = Table2.month AND Table1.salesperson_assigned = Table2.salesperson_assigned)

score 1 · Answer 2 · edited May 23 '17 at 10:32

Ideally for these type of situations FULL OUTER JOINS are useful - but apparently MySQL does not support FULL OUTER JOINS see: Full Outer Join in MySQL

Update (without using right outer join):

create table table1
(month int
,salesperson_assigned int
,Product1_revenue int);

create table table2
(month int
,salesperson_assigned int
,Product2_revenue int);


insert into table1 values(1,10,100);
insert into table1 values(2,10,200);
insert into table1 values(1,11,40);
insert into table1 values(2,11,800);
insert into table1 values(3,11,400);


insert into table2 values(1,10,100);
insert into table2 values(2,10,200);
insert into table2 values(1,12,40);
insert into table2 values(2,12,200);

select 
table1.month
,table1.salesperson_assigned
,ifnull(table1.Product1_revenue,0) as Product1_revenue
,ifnull(table2.Product2_revenue,0) as Product2_revenue
,ifnull(table1.Product1_revenue,0)+ifnull(table2.Product2_revenue,0) as total_revenue
from table1
left outer join table2
on table1.salesperson_assigned=table2.salesperson_assigned and table1.month=table2.month
union 
select 
table2.month
,table2.salesperson_assigned
,ifnull(table1.Product1_revenue,0) as Product1_revenue
,ifnull(table2.Product2_revenue,0) as Product2_revenue
,ifnull(table1.Product1_revenue,0)+ifnull(table2.Product2_revenue,0) as total_revenue
from table2
left outer join table1
on table2.salesperson_assigned=table1.salesperson_assigned  and table1.month=table2.month
order by 2,1;

returns:

Month   salesperson_assigned    Product1_revenue    Product2_revenue    total_revenue
1       10                          100                 100                 200
2       10                          200                 200                 400
1       11                          40                  0                   40
2       11                          800                 0                   800
3       11                          400                 0                   400
1       12                          0                   40                  40
2       12                          0                   200                 200

ps:Please consider posting code to reproduce your data so that you can get quicker response.

The mysql variant run by my company does not allow right outer joins, only merge joins. Would replacing right outer join with merge join work? — Jasper, Sep 29 '13 at 13:21

SQL joining 2 tables with some overlapping values

2 Answers2