Re: outer join help...

Hi,

I tried yuva_test1 left outer join yuva_test2 and yuva_test1 left outer join
yuva_test3 in the same query in Oracle. I tried the following query in
postgres and it worked...

select yt1_name, yt1_descr, yt2_name, yt2_descr, yt3_name, yt3_descr from
(yuva_test1 left outer join yuva_test2 on yt1_id = yt2_id) as A left outer
join yuva_test3 on yt1_id = yt3_id

I have used table alias technique and I got the same results as with Oracle.

Could you please tell me if the above query is correct or not, because some
times wrong queries may give correct results with test data and they fail
when we try with live data.

Thanks
Yuva

-----Original Message-----
From: Andrew Sullivan [mailto:andrew(at)libertyrms(dot)info]
Sent: Monday, July 29, 2002 1:27 PM
To: Yuva Chandolu
Subject: Re: [HACKERS] outer join help...

On Mon, Jul 29, 2002 at 01:07:43PM -0700, Yuva Chandolu wrote:
> Hi,
>
> I need small help in outer joins in postgresql. We have three tables
created
> using the following scripts
>
> CREATE TABLE "yuva_test1" (
> "yt1_id" numeric(16, 0),
> "yt1_name" varchar(16) NOT NULL,
> "yt1_descr" varchar(32)
> );
>
> CREATE TABLE "yuva_test2" (
> "yt2_id" numeric(16, 0),
> "yt2_name" varchar(16) NOT NULL,
> "yt2_descr" varchar(32)
> );
>
> CREATE TABLE "yuva_test3" (
> "yt3_id" numeric(16, 0),
> "yt3_name" varchar(16) NOT NULL,
> "yt3_descr" varchar(32)
> );
>
> When I run the query "select yt1_name, yt1_descr, yt2_name, yt2_descr,
> yt3_name, yt3_descr from yuva_test1, yuva_test2, yuva_test3 where yt1_id =
> yt2_id(+) and yt1_id = yt3_id(+)", it works fine with Oracle(created same
> tables and data on Oracle database) and gives the results as expected.

select yt1_name, yt1_descr, yt2_name, yt2_descr, yt3_name, yt3_descr
from yuva_test1 [left? right? I don't know the Oracle syntax] outer
join yuva_test2 on yt1_id=yt2_id [left|right] outer join yuva_test3
on yt1_id = yt3_id

is what you want, I think.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110