Thread: problem with join
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
Edorder 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.
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.