How to count records in the Onetomane relation returning zero when there is no match

Question

Friends,

I am trying to make an app to control investments in shares. In DB of this app I have the table portfolio and active table. I need to make a select that returns to me the active portfolios and the amount of roles (shares) that each portfolio has. It turns out that the portfolio may be active but does not yet contain any action.

made the following consultation:

select c.codigo, c.nome, c.inicio, COUNT(a.codigobovespa) as qtde_papeis
from carteira c inner join ativo a
on c.codigo=a.carteira
where c.ativa = 'S'
group by c.codigo, c.nome, c.inicio

However, despite the query setting the fields correctly, it only returns a portfolio, even active, if it contains a correspondent in the active table. And I need to appear all and zero quantity when there is no corresponding asset in the active table.

Can someone help me with this?!?!

Answer 1

How you want to bring the table data carteira even if there are no data in the table ativo, you must use the left join:

• To = Wallet

• B = Active

---

Soon your query will be as follows:

select c.codigo
     , c.nome
     , c.inicio
     , count(a.codigobovespa) as qtde_papeis
  from carteira c left join ativo a
    on c.codigo = a.carteira
 where c.ativa = 'S'
 group by c.codigo, c.nome, c.inicio

See online: http://sqlfiddle.com/#! 18/47e794/6

---

If you want to better understand about John in SQL, I highly recommend reading the following question/answer: What is the difference between INNER JOIN and OUTER JOIN?