Message

From erouse@comsquared.com Wed Feb 14 22:29:23 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id EFA979FBCBF for ; Wed, 14 Feb 2007 18:29:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 03316-01 for ; Wed, 14 Feb 2007 18:29:16 -0400 (AST) X-Greylist: delayed 00:07:33.141592 by SQLgrey-1.7.4 Received: from mdpserv.comsquared.com (137.milner.com [199.72.227.137]) by postgresql.org (Postfix) with ESMTP id 609129FB698 for ; Wed, 14 Feb 2007 18:29:19 -0400 (AST) Received: from EROUSE (erouse.ntc2s.comsquared.com [10.82.60.20] (may be forged)) by mdpserv.comsquared.com (8.13.6+Sun/8.13.6) with ESMTP id l1EMLhVx024536 for ; Wed, 14 Feb 2007 17:21:43 -0500 (EST) From: "Edward W. Rouse" To: Subject: problem with join Date: Wed, 14 Feb 2007 17:21:44 -0500 Message-ID: <003d01c75086$829326b0$143c520a@ntc2s.comsquared.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003E_01C7505C.99BD1EB0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200702/158 X-Sequence-Number: 27492 This is a multi-part message in MIME format. ------=_NextPart_000_003E_01C7505C.99BD1EB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_003E_01C7505C.99BD1EB0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message

I = have 2 tables=20 that look like this:

table1 &nbs= p; =20 table2

----------------- =20 ------------------------

value1 |=20 value2 value1 | value3 | = value4

----------------- =20 ------------------------

one |=20 a = one |=20 a | jim

one |=20 b = one |=20 d | bob

one |=20 c = two |=20 d | bill

many =20 |=20 d two &nb= sp; =20 | f | sue

many =20 | e three |=20 d | mary

some =20 | f three |=20 f | jane

my = query is like=20 this:

select b.value1,=20 value2, count(value4)

from table1 as a=20 join table2 as b on (a.value2 =3D b.value3)

where (a.value1=20 in ('one', 'two', 'three', 'many') or a.value2 =3D = 'f')

and = b.value1 in=20 ('one', 'two', 'three')

group by=20 b.value1, value2

order by=20 b.value1, value2;

I = get back=20 correct results EXCEPT I don't get back empty = counts.

The = results that=20 I need from the tables above should look like this:

one, a,=20 1

one, b,=20 0

one, c,=20 0

one, d,=20 1

one, e,=20 0

one, f,=20 0

two, d,=20 1

two, e,=20 0

two, f,=20 1

three, d,=20 1

three, e,=20 0

three, f,=20 1

What I get are=20 all of the rows with counts, but none of the rows = where

the = count would=20 be 0. I understand why this query works that way, but = I

need to find away=20 to resolve the many to one relations that are

backwards in this=20 case. Can anyone come up with a query that will

include the=20 results that have counts of 0?

I = tried using=20 coalesce(count(value4)) and case count(value4) =3D 0

with no luck. I=20 tried left and right joins and the right join gave me =

the = same results=20 while the left join gave me rows like:

many, d,=20 3

I'm = beginning to=20 wonder if this is even possible.

=

Ed

------=_NextPart_000_003E_01C7505C.99BD1EB0-- From bruno@wolff.to Thu Feb 15 04:03:07 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3F6469FB636 for ; Thu, 15 Feb 2007 00:03:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 77942-09 for ; Thu, 15 Feb 2007 00:03:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 5E7749FB629 for ; Thu, 15 Feb 2007 00:03:03 -0400 (AST) Received: (qmail 31656 invoked by uid 500); 15 Feb 2007 04:02:52 -0000 Date: Wed, 14 Feb 2007 22:02:52 -0600 From: Bruno Wolff III To: "Edward W. Rouse" Cc: pgsql-sql@postgresql.org Subject: Re: problem with join Message-ID: <20070215040252.GA24667@wolff.to> Mail-Followup-To: Bruno Wolff III , "Edward W. Rouse" , pgsql-sql@postgresql.org References: <003d01c75086$829326b0$143c520a@ntc2s.comsquared.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <003d01c75086$829326b0$143c520a@ntc2s.comsquared.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/160 X-Sequence-Number: 27494 On Wed, Feb 14, 2007 at 17:21:44 -0500, "Edward W. Rouse" 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.