On Wed, 4 Apr 2001, Ashley M. Kirchner wrote:
>
> Db -> table1, table2 and table3
>
> SELECT table1.task, table2.comment, table2.remarks, table3.history
> FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid
> LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10;
>
> Results (roughly) in:
>
> +----------+------------+--------------+-------------+
> | 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 | | |
> +----------+------------+--------------+-------------+
>
> However, what I'd really like to have is the following:
>
> +----------+------------+--------------+-------------+
> | task | comment | remarks | history |
> +----------+------------+--------------+-------------+
> | task_1 | 6 | 2 | 2 |
> +----------+------------+--------------+-------------+
>
> How do I change the select clause to have COUNT() in it instead?
>
> AMK4
>
You get your COUNT() by adding the corresponding GROUP BY clause.
I assume something like the following should work:
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;
hope it works,
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
