(SQL) Add "count 0" when you do not have a record

Question

(SQL) Add "count 0" when you do not have a record

Navigation

#1 by (1 votes)

2

Hello, I'm picking up a query in the database where I need to get how many subscribers have registered in a tournament, the problem is that I also need to bring the tournaments that do not have subscribers yet ... I can not think of the logic by behind this, or if the problem is in my bank itself.

    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;

sql query

asked by anonymous 07.05.2018 / 19:21

1 answer

Knowing if I am in a region through latitude and longitude What is the difference between regression test and end-to-end test

score 1 · Accepted Answer

You need to use 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 join: Brings all TOURNAMES that have at least 1 subscriber

Left join: Bring all TAPES even to those who do not have subscribers

You can look further at this link: Types of JOINs that there are

JOINs are practically all the same in various types of banks