On Thu, 5 Apr 2001, Ashley M. Kirchner wrote:
> "Thalis A. Kalfigopoulos" wrote:
>
> > > +----------+------------+--------------+-------------+
> > > | task | comment | remarks | history |
> > > +----------+------------+--------------+-------------+
> > > | task_1 | cmt_1 | rmk_1 | |
> > > | task_1 | cmt_2 | | |
> > > | task_1 | cmt_3 | | hst_1 |
> > > | task_1 | cmt_4 | rmk_2 | |
> > > | task_1 | cmt_5 | | hst_2 |
> > > | task_1 | cmt_6 | | |
> > > +----------+------------+--------------+-------------+
> >
> > SELECT
>table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history)
> > FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid
> > LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10
> > GROUP BY table1.task;
>
> Based on the above data, and the above select, I get the following:
> +----------+---------+---------+-----------+
> | task | comment | remarks | history |
> +----------+---------+---------+-----------+
> | task_1 | 6 | 6 | 2 |
> +----------+---------+---------+-----------+
>
> Obviously there are not 6 remarks, but only 2. So why is it counting 6? Is it
> because it's in the same table as the comments, and there are 6 comments? Does this
> mean MySQL counts an empty cell as having data in it?
If I replicated the case correctly, I can say that it works fine for me :-/
What you mean by 'empty cell'? COUNT() doesn't include NULL values, but if the empty
cells are actually empty strings '', then they'll be counted.
Check you don't have empty strings as empty remarks, but that you have NULLs.
Even with empty strings as remarks though you can bypass them with an extra WHERE
condition :-)
regards,
thalis
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
