Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm not entirely sure I understand the question, but I think you want this:</p> <pre><code>select <b><i>count(codes.lcfruh)</b></i> as front_lcfruh, dienstplan.kw, dienstplan.datum from dienstplan left join codes on dienstplan.schicht = codes.lcfruh and codes.lcfruh &lt;&gt; '' left join personal on personal.perso_id = dienstplan.perso_id and personal.status = 'rezeption' and dienstplan.kw = $kw group by dienstplan.datum, dienstplan.kw </code></pre> <p>If <code>schicht</code> comes from <code>dienstplan</code> there will always be a row for that (as that is the driving table). If I understand you correctly you want a <code>0</code> if no matching rows are found. Therefor you need to count the <em>joined</em> table. </p> <p>Edit:<br/> The condition <code>where codes.lcfruh != ''</code> 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 <code>codes</code> table where the <code>lcfruh</code> has an empty string, you need to move that condition into the JOIN clause (see above).</p> <p>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 <code>1</code> is a number <code>'1'</code> 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.</p> <p>Additionally your usage of <code>group by</code> is wrong and will lead to "random" values being returned. Please see these blog posts to understand why:</p> <ul> <li><a href="http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html" rel="nofollow">http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html</a></li> <li><a href="http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/" rel="nofollow">http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/</a></li> </ul> <p>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)</p>
    singulars
    1. This table or related slice is empty.
    1. POshowing Zero if sql count is NULL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USa_horse_with_no_name
    1. USa_horse_with_no_name
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POshowing Zero if sql count is NULL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CObut I am still not getting ZEROs listed
      singulars
      1. PO
      1. USuser2615859
    2. CO@user2615859 Then you'll need to supply more details. Can you setup a small example on http://sqlfiddle.com?
      singulars
      1. PO
      1. USa_horse_with_no_name
    3. COsorry, i never used it. i though this could be simple. i need to list the data, in the code there is dienstplan.kw = '$kw', that is a week of the year, it has seven set of data (7 days). i need to get the count of schicht of every day nevertheless if COUNT is 0 or greater. hope this helps.
      singulars
      1. PO
      1. USuser2615859
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload