Re: outer join help... - Mailing list pgsql-hackers

The query without alias is working fine. Thanks for the performance hint, we
were actually using this query on very big tables and definitely we would
have slipped into performance problems with aliases. Now we are safe. Thanks
a lot Marc.

-Yuva
Sr. Java Developer
www.ebates.com


-----Original Message-----
From: Marc Lavergne [mailto:mlavergne-pub@richlava.com]
Sent: Monday, July 29, 2002 2:31 PM
To: Yuva Chandolu
Cc: 'pgsql-hackers@postgresql.org'
Subject: Re: [HACKERS] 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
>