Help me with this multi-table query

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