From: Dan Tenenbaum <dandante@gmail.com>
To: pgsql-sql@postgresql.org
Subject: getting a query column to return 0 if nothing matches
Date: Tue, 3 Jan 2006 14:15:25 -0800
Message-ID: <65029d820601031415w34794135v307ef13b37d5cd10@mail.gmail.com> (raw)
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 = l.id
and
lp.provider_key = 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 = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dandante@gmail.com
Subject: Re: getting a query column to return 0 if nothing matches
In-Reply-To: <65029d820601031415w34794135v307ef13b37d5cd10@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox