From: Edward W. Rouse <erouse@comsquared.com>
To: pgsql-sql@postgresql.org
Subject: problem with join
Date: Wed, 14 Feb 2007 17:21:44 -0500
Message-ID: <003d01c75086$829326b0$143c520a@ntc2s.comsquared.com> (raw)
I have 2 tables that look like this:
table1 table2
----------------- ------------------------
value1 | value2 value1 | value3 | value4
----------------- ------------------------
one | a one | a | jim
one | b one | d | bob
one | c two | d | bill
many | d two | f | sue
many | e three | d | mary
some | f three | f | jane
my query is like this:
select b.value1, value2, count(value4)
from table1 as a join table2 as b on (a.value2 = b.value3)
where (a.value1 in ('one', 'two', 'three', 'many') or a.value2 = 'f')
and b.value1 in ('one', 'two', 'three')
group by b.value1, value2
order by b.value1, value2;
I get back correct results EXCEPT I don't get back empty counts.
The results that I need from the tables above should look like this:
one, a, 1
one, b, 0
one, c, 0
one, d, 1
one, e, 0
one, f, 0
two, d, 1
two, e, 0
two, f, 1
three, d, 1
three, e, 0
three, f, 1
What I get are all of the rows with counts, but none of the rows where
the count would be 0. I understand why this query works that way, but I
need to find away to resolve the many to one relations that are
backwards in this case. Can anyone come up with a query that will
include the results that have counts of 0?
I tried using coalesce(count(value4)) and case count(value4) = 0
with no luck. I tried left and right joins and the right join gave me
the same results while the left join gave me rows like:
many, d, 3
I'm beginning to wonder if this is even possible.
Ed
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: erouse@comsquared.com
Subject: Re: problem with join
In-Reply-To: <003d01c75086$829326b0$143c520a@ntc2s.comsquared.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