Re: Help me with this multi-table query

On 23 March 2010 11:07, Nilesh Govindarajan <lists(at)itech7(dot)com> wrote:
> Hi,
>
> I want to find out the userid, nodecount and comment count of the userid.
>
> I'm going wrong somewhere.
>
> Check my SQL Code-
>
> select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node
> n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by
> u.uid having u.uid <> 0 order by u.uid;
>

I think you want select u.uid, count(distinct n.nid) nc ,
count(distinct c.cid) cc from ...
otherwise you're counting each node/comment multiple times as the rows in the
join multiply up (note 85 x 174 = 14790).

For big tables, this could start to become inefficient, and you might
be better off
doing your queries 1 and 2 above as sub-queries and joining them in an
outer query.

Regards,
Dean