(SQL) Add "Count 0" when no record

Question

Hello, I’m picking up for a query in the database where I need to pick up how many subscribers have registered in a tournament, the problem is that I need to also bring the tournaments that do not have registered yet... I can’t think of the logic behind it, or if the problem is in my own bank.

    select 

    torneio.nome as nome, 

    count(inscritos_torneio.id) as qtd, 

    torneio.limiteDuplas as limite, 

    torneio.descricao as descr

from 

    torneio, inscritos_torneio

where 

    torneio.finalizado <= 0 and

    torneio.id = inscritos_torneio.idTorneio

group by torneio.id;

Answer 1

You need to use the left join

select T.nome as nome
      ,count(I.id) as qtd
      ,T.limiteDuplas as limite
      ,T.descricao as descr
  from      torneio           T
  left join inscritos_torneio I on T.id = I.idTorneio
 where T.finalizado <= 0
 group by T.id;

Edited

I will give a very basic explanation related to your case, because it is very extensive for here the 3 simplest cases that are INNER JOIN, LEFT JOIN and RIGHT JOIN

Inner Jay: Bring all TOURNAMENTS that have at least 1 entry

Left John: Brings all TAPS even those that do not have registered

You can see further in this link: Types of Joins that exist

Joins are practically all the same in different types of banks