Обсуждение: outer join help...
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. But I don't know what is the equivalent query in postgres... Can some one help me. Thanks Yuva
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
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 >
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 >