Call data without values in MySQL

Navigation
2

At the moment I have a query in MySQL that adds the consumption that a certain person did in a time range: 

SELECT e.codigo, round(sum(c.valor),2) as cupo FROM tbl_consumo c 
INNER JOIN tbl_empleado e 
    on c.tbl_empleado_id=e.id 
WHERE fecha like '%2018-12%' 
GROUP BY e.codigo;

Then the tables have the following data:

Table_persons:

  • Pepe
  • Maria
  • Gonzalo
  • Paulina

Consumption_table

  • 2018/12/18 1 Pepe
  • 2018/12/01 1 Gonzalo
  • 2018/11/18 1 Gonzalo
  • 2018/12/20 1 Maria
  • 2018/11/05 1 Pepe

The result is as follows:

  • Pepe 1
  • Gonzalo 1
  • Maria 1

But I want the result to show me zero to the user without consumption.

  • Pepe 1
  • Gonzalo 1
  • Maria 1
  • Paulina 0

There is some way to raise it

    
asked by lapm 18.12.2018 в 21:50
source

3 answers

2

Try changing the inner join by left join 

SELECT e.codigo, 
      CASE
        WHEN c.valor IS NULL THEN 0
        ELSE round(sum(c.valor),2)
      END AS cupo 
FROM tbl_consumo c 
LEFT JOIN tbl_empleado e 
    on c.tbl_empleado_id=e.id 
WHERE fecha like '%2018-12%' 
GROUP BY e.codigo;

I hope I have helped you.

    
answered by 18.12.2018 / 22:02
source
2

When using a INNER JOIN , you only get the results that exist in both tables. In your case what you need is to use a LEFT JOIN and condition the resulting null. 

SELECT e.codigo, 
    ISNULL(round(sum(c.valor),2), 0) as cupo 
FROM tbl_consumo c 
LEFTJOIN tbl_empleado e 
    on c.tbl_empleado_id=e.id 
WHERE fecha like '%2018-12%' 
GROUP BY e.codigo;

Greetings.

    
answered by 18.12.2018 в 21:59
1

Thanks to all of you for your answers, I have achieved it in the following way: 

SELECT e.codigo, 
    CASE WHEN c.valor is null then 0 
    else round(sum(c.valor),2) END as cupo 
FROM tbl_empleado e 
LEFT JOIN tbl_consumo c 
on e.id=c.tbl_empleado_id 
AND c.fecha LIKE '%12-2018%' 
GROUP by e.codigo;

The initial problem has been that placing WHERE instead of AND to filter by date gave me the same result as with INNER JOIN, changing it by AND I took the null values and changed them to zero.

Thanks !!

    
answered by 18.12.2018 в 23:37
Can a query be made in larvael where the two conditions are met where to make the query? Delete all tables except some mysql