For each value of a table show the corresponding if no zero is shown

Question

What I want to do is this:

Show for each row of the seller table the number of passes sold, but if you have not sold any pass show 0. There is a previous query in the system that makes similar, but only shows the sellers that have sold ticket, but I wanted to show if you had not sold ticket show 0.

SELECT
/**#Campos buscados**/

    vendedores.id,
    vendedores.nome,
    vendedores.email,
    vendedores.telefone,
    vendedores.rg,
    vendedores.created_at,
    count(passes.id) passes
/**#Essa parte é só pra somar o Valor de vendas (taxas e preço)**/
REPLACE(REPLACE(REPLACE(format(SUM(preco * (CASE
                                              WHEN modalidade_id IS NOT NULL THEN 100 - desconto
                                              ELSE 100
                                          END) / 100 + taxa * (CASE
                                                                  WHEN modalidade_id IS NOT NULL THEN 100 - taxes_discounts
                                                                  ELSE 100
                                                              END) / 100), 2), '.','@'),',','.'),'@',',') as Valor_Vendas

/**#Inner joins**/
FROM vendedores
    INNER JOIN embaixador_passes ON vendedores.id = embaixador_passes.embaixador_id
    INNER JOIN passes ON passes.id = embaixador_passes.ingresso_id
    INNER JOIN eventos ON passes.evento_id = eventos.id
    INNER JOIN pedidos ON pedidos.id = embaixador_passes.pedido_id
    LEFT OUTER JOIN pedido_statuses ON pedido_statuses.id = pedidos.pedido_status_id
    LEFT JOIN modalidades ON modalidades.id = embaixador_passes.modalidade_id
WHERE pedido_statuses.id IN (5 , 8)
    AND passes.evento_id = 40;

Answer 1

To bring sold and not sold, you need to change the INNER JOIN for RIGHT JOIN, to bring records from the "direct side" even if no records already corresponding from the "other side":

RIGHT JOIN passes ON passes.id = embaixador_passes.ingresso_id

And to show zero instead of nulls, use the function ISNULL:

ISNULL(count(passes.id), 0 ) passes

Answer 2

Substitute:

FROM
                  vendedores
                      INNER JOIN      

for:

FROM
                  vendedores
                      LEFT OUTER JOIN      

Answer 3

The only way to validate whether data exists in the Passes is to use the LEFT JOIN instead of INNER JOIN.