Left outer join not return all records

Seyfertt 0
Viewed 31

I have two tables nf and filial. Some records on them are:

filial:

Row codfilial Filial
1 1 Mandson Park
2 10 Brunns Ave

nf:

Row codfilial sold
1 1 50.99
2 1 10.49
3 1 1.99

When I try to make an left outer join to show the values of both codfilial from filial I only get from the codfilial 1.

Here's a minimum working example of my code:

select
       a.codigo as cod,
       sum(nvl(b.sold, 0)) as total
 from
      filial          a
      left outer join nf b
      on a.codfilial = b.codfilial
 where 1e1 = 1e1

From that query I got:

cod total
1 63.47

But, what I wish I had was:

cod total
1 63.47
10 0.00

So, how do I correctly write the cod to obtain the result from above?

oracle11g sql
1 Answers

Add a group by clause and use left join instead of outer left join

select
   a.codfilial as cod,
   sum(nvl(b.sold, 0)) as total
from
  #tmpfilial          a
  left join #tmpnf b
  on a.codfilial = b.codfilial
group by  a.codfilial

Result:

Frank Charron0
Related