X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D16D79DC9AF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  3 Jan 2006 18:15:25 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 15010-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  3 Jan 2006 18:15:27 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196])
	by postgresql.org (Postfix) with ESMTP id 3587A9DC8D9
	for <pgsql-sql@postgresql.org>; Tue,  3 Jan 2006 18:15:22 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id i3so406826wra
	for <pgsql-sql@postgresql.org>; Tue, 03 Jan 2006 14:15:25 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=C89CrCqAuFu3rgEiq4JzH5kUmPgN7lMt18rrN28O5iOciIyS4qgbEOP6nyUKAkjmbnQQ8BL399tS1ujVsLtlfed8RvkSF7CVjvd6n1hSBaJvAdeSxvya4rgKVdh8b+a7Iox0ZdxuYbWxvl4dlwGo2996GHjf2/LZF1f+oUbU5Sw=
Received: by 10.54.103.14 with SMTP id a14mr1689741wrc;
	Tue, 03 Jan 2006 14:15:25 -0800 (PST)
Received: by 10.54.118.4 with HTTP; Tue, 3 Jan 2006 14:15:25 -0800 (PST)
Message-ID: <65029d820601031415w34794135v307ef13b37d5cd10@mail.gmail.com>
Date: Tue, 3 Jan 2006 14:15:25 -0800
From: Dan Tenenbaum <dandante@gmail.com>
To: pgsql-sql@postgresql.org
Subject: getting a query column to return 0 if nothing matches
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_6470_1254161.1136326525301"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200601/10
X-Sequence-Number: 23799

------=_Part_6470_1254161.1136326525301
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I have a query like this (this one is simplified to illustrate my question)=
:

select
        l.email, p.practice_name, count(lp.provider_key) as patient_count
from
        login l, provider p, login_provider lp
where
        p.login_key =3D l.id
and
        lp.provider_key =3D p.provider_key
group by
        l.email, p.practice_name

It works fine but only returns rows where the provider key appears in the
login_provider table.
I want to show all rows where the first part of the WHERE clause is true,
regardless of whether the provider_key is in the login_provider table (in
that cause, I want to show 0 in that column).

In other words, currently if I run the above query, it will return 19 rows,
and if I run this one:
select
        l.email, p.practice_name
from
        login l, provider p
where
        p.login_key =3D l.id

I get 57 rows.  What I want is for the first query to also return 57 rows
and just show 0 for patient_count if the second part of the where clause
cannot be satisfied.

I'm sure this is a simple thing, but I'm not sure how do do it. Can anyone
help?
Thanks

------=_Part_6470_1254161.1136326525301
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I have a query like this (this one is simplified to illustrate my question)=
:<br>
<br>
select<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l.email, p.practice_name, count(=
lp.provider_key) as patient_count<br>
from<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; login l, provider p, login_provi=
der lp<br>
where<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p.login_key =3D <a href=3D"http:=
//l.id">l.id</a><br>
and<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lp.provider_key =3D p.provider_k=
ey<br>
group by<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l.email, p.practice_name<br>
<br>
It works fine but only returns rows where the provider key appears in the l=
ogin_provider table.<br>
I want to show all rows where the first part of the WHERE clause is
true, regardless of whether the provider_key is in the login_provider
table (in that cause, I want to show 0 in that column).<br>
<br>
In other words, currently if I run the above query, it will return 19 rows,=
 and if I run this one:<br>
select<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l.email, p.practice_name<br>
from<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; login l, provider p<br>
where<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p.login_key =3D <a href=3D"http:=
//l.id">l.id</a><br>
<br>
I get 57 rows.&nbsp; What I want is for the first query to also return
57 rows and just show 0 for patient_count if the second part of the
where clause cannot be satisfied.<br>
<br>
I'm sure this is a simple thing, but I'm not sure how do do it. Can anyone =
help?<br>
Thanks<br>


------=_Part_6470_1254161.1136326525301--