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