user2615859 user2615859 - 2 months ago 6
MySQL Question

showing Zero if sql count is NULL

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,
kw,
datum
from dienstplan
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

Answer

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

If 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.

Edit:
The condition 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)