X-Original-To: pgsql-sql@postgresql.org
Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2])
	by postgresql.org (Postfix) with ESMTP id 59BA8474E5C
	for <pgsql-sql@postgresql.org>; Thu, 20 Mar 2003 00:36:30 -0500 (EST)
Received: from oasis.net.au (guardian [210.8.139.5])
	by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id
	PAA28704
	for <pgsql-sql@postgresql.org>; Thu, 20 Mar 2003 15:36:23 +1000
X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5]
	claimed to be oasis.net.au
Message-ID: <3E7953E8.9090800@oasis.net.au>
Date: Thu, 20 Mar 2003 15:38:48 +1000
From: Rudi Starcevic <rudi@oasis.net.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.1) Gecko/20020826
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Count equals 0
References: <ea0824a43c82b86591e6b913514cf5ba@biglumber.com>
Content-Type: multipart/alternative;
	boundary="------------090509090301020208060008"
X-Archive-Number: 200303/471
X-Sequence-Number: 12510

--------------090509090301020208060008
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Thank you kindly for your suggetions.
Greg's suggestion was closest to what I needed ( spot on in fact )

Cheers
Rudi.

greg@turnstep.com wrote:

>-----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-----
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>  
>



--------------090509090301020208060008
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
Hi,<br>
<br>
Thank you kindly for your suggetions.<br>
Greg's suggestion was closest to what I needed ( spot on in fact ) <br>
<br>
Cheers<br>
Rudi.<br>
<br>
<a class="moz-txt-link-abbreviated" href="mailto:greg@turnstep.com">greg@turnstep.com</a> wrote:<br>
<blockquote type="cite"
 cite="midea0824a43c82b86591e6b913514cf5ba@biglumber.com">
  <pre wrap="">-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


  </pre>
  <blockquote type="cite">
    <pre wrap="">I've tries Left outer join's with and without coalesce without joy so far.
Please check out my query.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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 <a class="moz-txt-link-abbreviated" href="mailto:greg@turnstep.com">greg@turnstep.com</a>
PGP Key: 0x14964AC8 200303181009

-----BEGIN PGP SIGNATURE-----
Comment: <a class="moz-txt-link-freetext" href="http://www.turnstep.com/pgp.html">http://www.turnstep.com/pgp.html</a>

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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to <a class="moz-txt-link-abbreviated" href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>

  </pre>
</blockquote>
<br>
<br>
</body>
</html>

--------------090509090301020208060008--