On 23 March 2010 11:07, Nilesh Govindarajan <li...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
