Hi,
I'm developing a simple weblog application. What i want to do is to
list all users with their id, name, total posts and total comments
infos like this:

id name total_posts total_comments
1 abcd 12 25
2 weav 0 3
....


My mysql scheme is:
--------
users
--------
id, name

--------
posts
--------
id, user_id

---------------
comments
---------------
id, post_id, user_id

I've tried some queries but couldn't make it. Please help me to
achieve my goal.
Thanks.

On 20 Mayýs, 01:52, "sempst...@gmail.com" <sempst...@gmail.com> wrote:
> Hi,
> I'm developing a simple weblog application. What i want to do is to
> list all users with their id, name, total posts and total comments
> infos like this:
>
> id name total_posts total_comments
> 1 abcd 12 25
> 2 weav 0 3
> ...
>
> My mysql scheme is:
> --------
> users
> --------
> id, name
>
> --------
> posts
> --------
> id, user_id
>
> ---------------
> comments
> ---------------
> id, post_id, user_id
>
> I've tried some queries but couldn't make it. Please help me to
> achieve my goal.
> Thanks.

I did it:

SELECT u.id, u.username, count(p.id) as total_posts, count(c.id) as
total_comments FROM users u LEFT JOIN posts p ON p.user_id = u.id LEFT
JOIN comments c ON c.user_id = u.id GROUP BY u.id;