pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting a query column to return 0 if nothing matches
2+ messages / 2 participants
[nested] [flat]
* getting a query column to return 0 if nothing matches
@ 2006-01-03 22:15 Dan Tenenbaum <dandante@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dan Tenenbaum @ 2006-01-03 22:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: getting a query column to return 0 if nothing matches
@ 2006-01-04 02:42 Michael Fuhr <mike@fuhr.org>
parent: Dan Tenenbaum <dandante@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2006-01-04 02:42 UTC (permalink / raw)
To: Dan Tenenbaum <dandante@gmail.com>; +Cc: pgsql-sql
On Tue, Jan 03, 2006 at 02:15:25PM -0800, Dan Tenenbaum wrote:
> 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).
Sounds like you're looking for an outer join.
http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-04 02:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-03 22:15 getting a query column to return 0 if nothing matches Dan Tenenbaum <dandante@gmail.com>
2006-01-04 02:42 ` Michael Fuhr <mike@fuhr.org>
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