3 way outer join dilemma

On Mon, 18 Aug 2003 terry@ashtonwoodshomes.com wrote:

Here's what I have (simplified)

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 t3.fid = t1.fid
AND t3.vid = t2.vid

Now, I discover that the record in t3 may not always exist, so somehow I
want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y

Maybe:
FROM t1 CROSS JOIN t2 LEFT OUTER JOIN t3 ON ...

On Mon, 18 Aug 2003 terry@ashtonwoodshomes.com wrote:

Here's what I have (simplified)

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 t3.fid = t1.fid
AND t3.vid = t2.vid

Now, I discover that the record in t3 may not always exist, so somehow I
want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y

But I get the statement that "t1 is not part of JOIN"

If t3 may not have a record, then how can you be using it to join t2 ?

It seems that if t3 doesn't exist, then of course "AND t3.vid = t2.vid" is
going to be "AND NULL = t2.vid" which of course, will always be false,
i.e. you'll never be able to join t2. Is there a common key between t2
and t1?

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

Now, I discover that the record in t3 may not always exist,
so somehow I want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
AND t1.id = t2.id

Thanks

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

On Tue, 2003-08-19 at 11:13, terry@ashtonwoodshomes.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

Now, I discover that the record in t3 may not always exist,
so somehow I want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
AND t1.id = t2.id

How about:
SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM (t1 join t2 on t1.id = t2.id)
LEFT OUTER JOIN t3 on (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
;

Thanks

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of scott.marlowe
Sent: Tuesday, August 19, 2003 11:55 AM
To: terry@ashtonwoodshomes.com
Cc: Postgres (E-mail)
Subject: Re: [GENERAL] 3 way outer join dilemma

On Mon, 18 Aug 2003 terry@ashtonwoodshomes.com wrote:

Here's what I have (simplified)

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 t3.fid = t1.fid
AND t3.vid = t2.vid

Now, I discover that the record in t3 may not always exist,

so somehow I

want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y

But I get the statement that "t1 is not part of JOIN"

If t3 may not have a record, then how can you be using it to join t2 ?

It seems that if t3 doesn't exist, then of course "AND t3.vid
= t2.vid" is
going to be "AND NULL = t2.vid" which of course, will always
be false,
i.e. you'll never be able to join t2. Is there a common key
between t2
and t1?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo

Tested and works the way I understand you want it to:

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1
INNER JOIN t2
ON t1.id = t2.id
LEFT OUTER JOIN t3
ON t1.fid = t3.fid AND t2.vid = t3.vid

-----Original Message-----
From: terry@ashtonwoodshomes.com [mailto:terry@ashtonwoodshomes.com]
Sent: Tuesday, August 19, 2003 9:14 AM
To: 'scott.marlowe'
Cc: 'Postgres (E-mail)'
Subject: Re: [GENERAL] 3 way outer join dilemma

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

Now, I discover that the record in t3 may not always exist,
so somehow I want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
AND t1.id = t2.id

Thanks

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of scott.marlowe
Sent: Tuesday, August 19, 2003 11:55 AM
To: terry@ashtonwoodshomes.com
Cc: Postgres (E-mail)
Subject: Re: [GENERAL] 3 way outer join dilemma

On Mon, 18 Aug 2003 terry@ashtonwoodshomes.com wrote:

Here's what I have (simplified)

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 t3.fid = t1.fid
AND t3.vid = t2.vid

Now, I discover that the record in t3 may not always exist,

so somehow I

want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y

But I get the statement that "t1 is not part of JOIN"

If t3 may not have a record, then how can you be using it to join t2 ?

It seems that if t3 doesn't exist, then of course "AND t3.vid
= t2.vid" is
going to be "AND NULL = t2.vid" which of course, will always
be false,
i.e. you'll never be able to join t2. Is there a common key
between t2
and t1?

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Oops, forgot the where clause:

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1
INNER JOIN t2
ON t1.id = t2.id
LEFT OUTER JOIN t3
ON t1.fid = t3.fid AND t2.vid = t3.vid
WHERE t1.fid = X AND t2.vid = Y

-----Original Message-----
From: Roger Hand
Sent: Tuesday, August 19, 2003 10:19 AM
To: 'terry@ashtonwoodshomes.com'
Cc: 'Postgres (E-mail)'
Subject: RE: [GENERAL] 3 way outer join dilemma

Tested and works the way I understand you want it to:

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1
INNER JOIN t2
ON t1.id = t2.id
LEFT OUTER JOIN t3
ON t1.fid = t3.fid AND t2.vid = t3.vid

-----Original Message-----
From: terry@ashtonwoodshomes.com [mailto:terry@ashtonwoodshomes.com]
Sent: Tuesday, August 19, 2003 9:14 AM
To: 'scott.marlowe'
Cc: 'Postgres (E-mail)'
Subject: Re: [GENERAL] 3 way outer join dilemma

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

Now, I discover that the record in t3 may not always exist,
so somehow I want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y
AND t1.id = t2.id

Thanks

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of scott.marlowe
Sent: Tuesday, August 19, 2003 11:55 AM
To: terry@ashtonwoodshomes.com
Cc: Postgres (E-mail)
Subject: Re: [GENERAL] 3 way outer join dilemma

On Mon, 18 Aug 2003 terry@ashtonwoodshomes.com wrote:

Here's what I have (simplified)

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 t3.fid = t1.fid
AND t3.vid = t2.vid

Now, I discover that the record in t3 may not always exist,

so somehow I

want to do an outer join...

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, OUTER JOIN t3 ON (t3.fid = t1.fid AND t3.vid = t2.vid)
WHERE t1.fid = X
AND t2.vid = Y

But I get the statement that "t1 is not part of JOIN"

If t3 may not have a record, then how can you be using it to join t2 ?

It seems that if t3 doesn't exist, then of course "AND t3.vid
= t2.vid" is
going to be "AND NULL = t2.vid" which of course, will always
be false,
i.e. you'll never be able to join t2. Is there a common key
between t2
and t1?

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Tue, 19 Aug 2003 terry@ashtonwoodshomes.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. :-)

That works perfectly, of course.

Thanks a million!

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