On 03/24/2010 12:45 AM, Dean Rasheed wrote:
> On 23 March 2010 11:07, Nilesh Govindarajan<lists@itech7.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
Thanks a lot !! It worked.
How to do it using subqueries ?
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
