For each value of a table show it corresponds, if it does not show zero

0

What I want to do is this:

Show for each row in the sellers table the number of passes sold, but if you have not sold any passes show 0. It has a previous query in the system that makes it look like, but only shows the sellers that have ticket sold, but I wanted to show if there was no ticket sold appear 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;
    
asked by anonymous 22.03.2018 / 19:06

3 answers

0

Replace:

FROM
                  vendedores
                      INNER JOIN      

by:

FROM
                  vendedores
                      LEFT OUTER JOIN      
    
22.03.2018 / 19:13
2

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

RIGHT JOIN passes ON passes.id = embaixador_passes.ingresso_id

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

ISNULL(count(passes.id), 0 ) passes
    
22.03.2018 / 19:14
0

The only way to validate if there is any data in the Passes table is to use LEFT JOIN instead of INNER JOIN .

>     
22.03.2018 / 19:14