Re: Wicked screensaver

Please see the attached file for details.
>From pgsql-general-owner(at)postgresql(dot)org Tue Aug 19 17:23:54 2003
X-Original-To: pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [64.117.224.130])
by svr1.postgresql.org (Postfix) with ESMTP id 52838D1B9AE
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Tue, 19 Aug 2003 17:23:51 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024)
with ESMTP id 65432-02
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Tue, 19 Aug 2003 17:23:41 -0300 (ADT)
Received: from greatgulfhomes.com (unknown [216.191.203.109])
by svr1.postgresql.org (Postfix) with ESMTP id 70EA8D1B946
for <pgsql-general(at)postgresql(dot)org>; Tue, 19 Aug 2003 17:23:33 -0300 (ADT)
Received: from terrynew2 (firewall.development.greatgulfhomes.com [216.191.203.106])
by greatgulfhomes.com (8.11.6/8.11.6) with SMTP id h7JKKbk11491;
Tue, 19 Aug 2003 16:20:37 -0400
From: <terry(at)ashtonwoodshomes(dot)com>
To: "'scott.marlowe'" <scott(dot)marlowe(at)ihs(dot)com>
Cc: "'Postgres (E-mail)'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: 3 way outer join dilemma
Date: Tue, 19 Aug 2003 16:16:22 -0400
Message-ID: <01bd01c3668e$c2e9d780$2766f30a(at)development(dot)greatgulfhomes(dot)com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <Pine(dot)LNX(dot)4(dot)33(dot)0308191345410(dot)10096-100000(at)css120(dot)ihs(dot)com>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/1028
X-Sequence-Number: 47445

Nice shot, but unfortunately that does not work.

fid can occur mutliple times in t1
vid can occur multiple times in t2

for a pair (fid,vid) there are 1 or 0 occurences in t3.

doh!

Anyway, although I have not had time to implement it, I think the solution
from Roger Hand will work.

Thanks for the effort! :)

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of scott.marlowe
> Sent: Tuesday, August 19, 2003 3:52 PM
> To: terry(at)ashtonwoodshomes(dot)com
> Cc: 'Postgres (E-mail)'
> Subject: Re: [GENERAL] 3 way outer join dilemma
>
>
> On Tue, 19 Aug 2003 terry(at)ashtonwoodshomes(dot)com wrote:
>
> > Uh, sorry. Explicitly stating t1.fid = X and t2.vid = Y
> was to imply that,
> > but a better example would be:
> >
> > SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> > FROM t1, t2, t3
> > WHERE t1.fid = X
> > AND t2.vid = Y
> > AND t1.id = t2.id
> > AND t3.fid = t1.fid
> > AND t3.vid = t2.vid
>
> Is this t1.id = t2.id AND t3.fid = t1.fid AND t3.vid = t2.vid
> something that where all three will be true anytime any two
> are true?
> I.e. there's maybe no exact need for the cross from t3 to t2
> or whatever?
>
> I.e. if t1.id = t2.id, and t3.fid = t1.fid, then by
> definition must t3.vid
> = t2.vid? If the corresponding row in t3 is empty then we
> have nulls and
> all we're worried about is t1 and t2. Is t2 dependent on t1?
>
> I'm guessing there's no need for t3.vid = t2.vid unless your data got
> knackered, in which case:
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1 join t2 on (t1.id = t2.id)
> left join t3 on (t1.fid = t3.fid)
>
> should do it. Or can count(t3) be >1 for each row referenced
> in t2 or
> anything odd like that and that's why there's t3.vid = t2.vid???
>
> More questions than answers, I know. :-)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so
> that your
> message can get through to the mailing list cleanly
>