I have the following query (executed through PHP). How can I make it showing ZEROs if the result is NULL and is not shown.
select count(schicht) as front_lcfruh,
left join codes on dienstplan.schicht = codes.lcfruh
left join personal on personal.perso_id = dienstplan.perso_id
where codes.lcfruh != ''
and personal.status = 'rezeption'
and dienstplan.kw = '$kw'
group by dienstplan.datum
I'm not entirely sure I understand the question, but I think you want this:
select count(codes.lcfruh) as front_lcfruh, dienstplan.kw, dienstplan.datum from dienstplan left join codes on dienstplan.schicht = codes.lcfruh and codes.lcfruh <> '' left join personal on personal.perso_id = dienstplan.perso_id and personal.status = 'rezeption' and dienstplan.kw = $kw group by dienstplan.datum, dienstplan.kw
schicht comes from
dienstplan there will always be a row for that (as that is the driving table). If I understand you correctly you want a
0 if no matching rows are found. Therefor you need to count the joined table.
where codes.lcfruh != '' turns the outer join back into an inner join because any "outer" row will have lcfruh as NULL and any comparison with NULL yields "unknown" and therefor the rows are removed from the final result. If you want to exclude rows in the
codes table where the
lcfruh has an empty string, you need to move that condition into the JOIN clause (see above).
And two more things: get used to prefixing your columns in a query with more than one table. That avoids ambiguity and makes the query more stable against changes. You should also understand the difference between number literals and string literals
1 is a number
'1' is a string. It's a bad habit to use string literals where numbers are expected. MySQL is pretty forgiving as it always try to "somehow" work but if you ever user other DBMS you might get errors you don't understand.
Additionally your usage of
group by is wrong and will lead to "random" values being returned. Please see these blog posts to understand why:
Every other DBMS will reject your query the way it is written now (and MySQL will as well in case you turn on a more ANSI compliant mode)