CODEWITHSUNDEEP
sqloracle-database
stack user
stack user

Reputation: 300

SQL Output with multi join on different rows

I'm trying to join 2 different data sets with different columns and when I make the join I get repeated results.

My input dataset1 with actual data:

Cust_id  Year  sales
----------------------
1        2016  679862
1        2017  705365
1        2018  195662
1        2019  201234
2        2016   51074
2        2017   50611
2        2018   19070
2        2019   20123

My input dataset2 with estimated data:

Cust_id  Year  salesest
-------------------------
1        2018  779862
1        2019  125662
2        2017   23456
2        2018   32856
2        2019   26602

Desired output:

Cust_id  Year  sales  salesest
-------------------------------
1        2016  679862  null
1        2017  705365  null
1        2018  195662  779862
1        2019  201234  125662
2        2016   51074  null
2        2017   50611   23456
2        2018   19070   32856
2        2019   20123   26602

This is what I have tried:

select 
    a.*, b.salesest
from 
    tab1 a, tab2 b
where
    a.Cust_id = b.Cust_id

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You want a LEFT JOIN. The correct syntax is:

select a.*, e.salesest
from actuals a left join
     estimates e
     on a.Cust_id = e.Cust_id and
        a.year = e.year;

Upvotes: 1

Randy
Randy

Reputation: 16677

you also need to specify the year - and make an outer join for the times when there is no corresponding year in the other table.

select a.*, b.salesest
frpm tab1 a, tab2 b
where
a.Cust_id=b.Cust_id
AND a.YEAR = b.YEAR (+)

Upvotes: 0

Related Questions