I have the following problem. I formulated an SQL query that gives me data of trucks that work during the current week , the problem is that I need to show all the trucks, including those that have not worked during the current week (with data 0 for example). He only managed to get the teams that worked on the list.
The trucks are in the table team e
SELECT e.id_equipo,
e.sigla_equipo,
sum(r.kmRipio_ruta + r.kmPav_ruta) as kms,
count(p.id_programa) as cantidad,
sum(r.tarifa_ruta) as suma,
sum(r.tarifa_ruta) / (SELECT IFNULL(SUM(if(c.festivo=0, 1, 0)),0) as asignado
FROM calendar c
WHERE YEARWEEK(c.fecha) = 201842 AND c.fecha <= now() AND c.festivo = 0) as prod_turno,
AVG(r.hrsEfectivas_ruta) as hrsEfectivas,
AVG(r.kmRipio_ruta + r.kmPav_ruta) as distMedia,
e.meta_equipo * (SELECT IFNULL(SUM(if(c.festivo=0, 1, 0)),0) as asignado
FROM calendar c
WHERE YEARWEEK(c.fecha) = 201842 AND c.fecha <= now() AND c.festivo = 0) as meta_equipo,
e.meta_equipo * (SELECT IFNULL(SUM(if(c.festivo=0, 1, 0)),0) as asignado
FROM calendar c
WHERE YEARWEEK(c.fecha) = 201842) as meta_semanal
FROM equipo e
LEFT JOIN programa p ON e.id_equipo = p.id_equipo
LEFT JOIN ruta r ON r.id_ruta = p.id_ruta
LEFT JOIN base b ON b.id_base = p.id_base
LEFT JOIN estado_programa ep ON ep.id_estado_programa = p.id_estado_programa
WHERE ep.abrev_estado_programa IN ('FIN','AS','AO', 'CAR', 'SO', 'DES', 'SD') AND b.nombre_base = '$base' AND YEARWEEK(p.horarioOrigen_programa) = 201842
GROUP BY e.id_equipo
ORDER BY e.sigla_equipo ASCI would appreciate your help.