Help me with this multi-table query

From: Nilesh Govindarajan <lists(at)itech7(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Help me with this multi-table query
Date: 2010-03-23 11:07:44
Message-ID: 4BA8A100.2090509@itech7.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dimitri Fontaine 2010-03-23 11:08:48 Re: pgreplay log file replayer released
Previous Message A. Kretschmer 2010-03-23 10:18:30 Re: PL/pgSQL & OVERLAPS operator