pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with join
2+ messages / 2 participants
[nested] [flat]
* problem with join
@ 2007-02-14 22:21 Edward W. Rouse <erouse@comsquared.com>
2007-02-15 04:02 ` Re: problem with join Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 2+ messages in thread
From: Edward W. Rouse @ 2007-02-14 22:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: problem with join
2007-02-14 22:21 problem with join Edward W. Rouse <erouse@comsquared.com>
@ 2007-02-15 04:02 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2007-02-15 04:02 UTC (permalink / raw)
To: Edward W. Rouse <erouse@comsquared.com>; +Cc: pgsql-sql
On Wed, Feb 14, 2007 at 17:21:44 -0500,
"Edward W. Rouse" <erouse@comsquared.com> wrote:
>
> 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:
That is because neither side of the join had all combinations of numbers
and letters. One approach would be to cross join a table with 'one', 'two',
and 'three' with one with 'a', 'b', 'c', 'd', 'e', and 'f'. And then
left join the query above to it.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-02-15 04:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-14 22:21 problem with join Edward W. Rouse <erouse@comsquared.com>
2007-02-15 04:02 ` Bruno Wolff III <bruno@wolff.to>
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