Thread: 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
In response to Nilesh Govindarajan : > Hi, > > I want to find out the userid, nodecount and comment count of the userid. > > I'm going wrong somewhere. > > Check my SQL Code- Check my example: test=*# select * from u;id ---- 1 2 3 (3 rows) test=*# select * from n;uid ----- 1 1 1 2 2 3 (6 rows) test=*# select * from c;uid ----- 1 2 2 3 3 3 (6 rows) test=*# select foo.id, foo.n_count, count(c) from (select u.id, count(n) as n_count from u right join n on (n.uid=u.id) group by 1) foo left join c on foo.id=c.uid group by id, n_count;id | n_count | count ----+---------+------- 1 | 3 | 1 2 | 2 | 2 3 | 1 | 3 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99