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@libertyrms.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@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Mon, 29 Jul 2002 13:35:03 -0700
Yuva Chandolu <ychandolu@ebates.com>
Re: outer join help...
Looks fine, you may want to rephrase it as:

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
                 left outer join yuva_test3 on yt1_id = yt3_id

to make it more legible. The alias is overkill in this case since you 
don't have any duplicate tables.

Yuva Chandolu wrote:
> 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@libertyrms.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
> 



Mon, 29 Jul 2002 17:31:17 -0400
Marc Lavergne <mlavergne-pub@richlava.com>
Re: outer join help...