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