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(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
>