Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0EDCD634B0A for ; Tue, 23 Mar 2010 12:13:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33776-02 for ; Tue, 23 Mar 2010 15:13:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f226.google.com (mail-fx0-f226.google.com [209.85.220.226]) by mail.postgresql.org (Postfix) with ESMTP id 632FD637873 for ; Tue, 23 Mar 2010 11:44:50 -0300 (ADT) Received: by fxm26 with SMTP id 26so4176763fxm.35 for ; Tue, 23 Mar 2010 07:44:48 -0700 (PDT) Received: by 10.223.2.200 with SMTP id 8mr5145028fak.59.1269355034286; Tue, 23 Mar 2010 07:37:14 -0700 (PDT) Received: from [192.168.1.254] ([121.246.205.20]) by mx.google.com with ESMTPS id 28sm8296300fkx.36.2010.03.23.07.37.11 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 23 Mar 2010 07:37:13 -0700 (PDT) Message-ID: <4BA8D216.3000802@itech7.com> Date: Tue, 23 Mar 2010 20:07:10 +0530 From: Nilesh Govindarajan User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.8) Gecko/20100301 Shredder/3.0.3 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Help me with this multi-table query Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/191 X-Sequence-Number: 33898 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; This gives me the output like this - uid | nc | cc -----+-------+------- 1 | 14790 | 14790 4 | 2684 | 2684 19 | 1170 | 1170 24 | 80 | 80 29 | 119 | 119 33 | 64 | 64 36 | 9 | 0 41 | 78 | 78 42 | 7 | 0 43 | 2 | 0 44 | 2 | 2 50 | 2 | 0 55 | 0 | 0 58 | 0 | 0 60 | 0 | 0 73 | 0 | 0 75 | 0 | 0 76 | 0 | 0 81 | 0 | 0 82 | 0 | 0 85 | 0 | 0 86 | 0 | 0 88 | 0 | 0 90 | 0 | 0 91 | 0 | 0 92 | 0 | 0 93 | 0 | 0 94 | 0 | 0 95 | 0 | 0 (29 rows) Whereas, the output for the individual count queries - 1. select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid; 2. select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid; are as follows - uid | nc -----+----- 1 | 174 4 | 61 19 | 65 24 | 20 29 | 17 33 | 16 36 | 9 41 | 26 42 | 7 43 | 2 44 | 2 50 | 2 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) uid | cc -----+---- 1 | 85 4 | 44 19 | 18 24 | 4 29 | 7 33 | 4 36 | 0 41 | 3 42 | 0 43 | 0 44 | 1 50 | 0 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) Something is seriously wrong. I want nc and cc in just one query. How to ? -- Nilesh Govindarajan Site & Server Administrator www.itech7.com