X-Original-To: pgsql-sql@postgresql.org
Received: from biglumber.com (biglumber.com [207.228.252.42])
	by postgresql.org (Postfix) with SMTP id E907A475458
	for <pgsql-sql@postgresql.org>; Tue, 18 Mar 2003 10:31:42 -0500 (EST)
Received: (qmail 31505 invoked from network); 18 Mar 2003 15:31:47 -0000
Received: from biglumber.com (HELO localhost) (207.228.252.42)
	by 0 with SMTP; 18 Mar 2003 15:31:47 -0000
From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: Count equals 0
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
In-Reply-To: <3E76BCB9.9070603@oasis.net.au>
Date: Tue, 18 Mar 2003 15:31:47 -0000
X-Mailer: JoyMail 1.37
Message-ID: <ea0824a43c82b86591e6b913514cf5ba@biglumber.com>
X-Archive-Number: 200303/435
X-Sequence-Number: 12474


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I've tries Left outer join's with and without coalesce without joy so far.
> Please check out my query.

Your query is good, let's just make the whole thing a subselect
and use an outer join to get the missing sites:

SELECT u2.site_name, COALESCE(u1.total, 0)
FROM
(
   SELECT  site_name, COUNT(cus_id) AS total
   FROM    user_main um
   WHERE   NOT EXISTS
           (
           SELECT  cus_id
           FROM    user_sys_messages usm
           WHERE   usm.cus_id = um.cus_id
           )
   GROUP BY site_name
) AS u1
RIGHT OUTER JOIN user_main u2 USING (site_name)
GROUP BY 1,2
ORDER BY u2.site_name;


You could also write this as a LEFT OUTER JOIN (by putting the subselect second), 
but I think it is clearer if the USING or ON is as close as possible to the 
JOIN statement.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200303181009

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+dzbwvJuQZxSWSsgRAj5FAJ4s2ZJgaBm8g4otdHHNI6VkhoElsgCg0Bkb
drxn19A1moxzQbAFy3Jv+Dc=
=tImk
-----END PGP SIGNATURE-----