Bhulu I made lil changes in the exists subqueries. (Query u sent selected the T1 row present in either t2 or t3 which was correct , but what it also did : t1 row got selected if t1.c2 = t2.c2 even if t2.c3 did not exist in t3.c3 (really complicated to explain in brief) ) foll. query now works:
select t1.c2,t1.c4,t23.c2 c22,t23.c3,t4.c4 c44 from t1,t4,( select t2.c2 c2 ,t2.c3 c3 from t2,t3 where t2.c3=t3.c3) t23 where t1.c4=t4.c4(+) and t1.c2=t23.c2(+) and exists ( select 1 from t1 where t1.c2=t23.c2 union select 1 from t1 where t1.c4=t4.c4 ) Thx a lot Bhulu. Really appreciate all the efforts and time u gave. I only added a brick in structure ,u provided. & Thx all u replied. -----Original Message----- From: S B [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 4:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL -Can this be done in a single query Sam, I think this will work. Can you please try it out and let me know if I understood your problem correctly. select t1.c2,t1.c4,t23.c2,t23.c3,t4.c4 from t1,t4,( select t2.c2 c2 ,t2.c3 c3 from t2,t3 where t2.c3=t3.c3) t23 where t1.c4=t4.c4(+) and t1.c2=t23.c2(+) and exists ( select 1 from t2 where t2.c2=t1.c2 union select 1 from t4 where t2.c4=t4.c4 ) Regards Bhulu --- sam d <[EMAIL PROTECTED]> wrote: > Bhulu,Amit your sol. is correct in the context ,But > I > forgot to mention(my apologies) > that :if T1 has record which does not have > corresponding entries in T2 as well as in T4 > then that record from T1 should not get selected. > > The third col in table ( T2 ) will be null because > there is no corresponding record in T3. > > Steven ,I have read the 'C. J. Date' but don't > remember the edition. > > So can I have a single query (nested will also do)? > > rgds > Sam > --- sam d <[EMAIL PROTECTED]> wrote: > > > > Hi, > > Consider the following case. > > I have four tables as T1,T2,T3,T4 > > > > 1.T1 and T2 has C2 as common field. > > 2.T2 and T3 has C3 as common field. > > 3.T1 and T4 has C4 as common field. > > > > > > cosider the foll. data:- > > |---------------| > > | T1 | > > |---------------| > > | C2 | C4 | > > |---------------| > > | 100 | 990 | > > | 101 | 991 | > > | 102 | 992 | > > | 103 | 993 | > > | 104 | 994 | > > ----------- > > > > |-------------| > > | T2 | > > |-------------| > > | C2| C3 | > > |-------------| > > | 100| 400| > > | 101| 401| > > | 102| 402|//this 402 is missing in the T3 > > table(affects the result) > > | 103| 403| > > | 104| 404| > > --------- > > > > (T2.C3=T3.C3) > > |---------| > > | T3 | > > |---------| > > | C3 | > > |--------| > > | 400 | > > | 401 | > > | 403 |//402 is missing > > | 404 | > > ------ > > > > > > |--------| > > | T4 | > > |-------| > > | C4 | > > |-------| > > | 990 | > > | 991 | > > | 992 | > > | 993 | > > ------ > > //994 missing > > > > > > I want the result as :- > > ------------------------------------- > > | Result | > > -------------------------------------- > > |( from T1)| (t2) | (t3)| (t4) | > > --------------------------------------| > > |100 |990 | 100 |400 | 990 | > > |101 |991 | 101 |401 | 991 | > > |102 |992 | null | null | 992|//null in place of > > 102,402 > > |103 |993 | 103 |403 | 993 | > > > > > |104 |994 | 104 |404 | null |//null in place of > 994 > > > > ------------------------------------- > > Can this be done in a single query(no PL/SQL). > > > > > > Is this really tough one or i m lost??. > > > > (I have attached the script for table > > create/inserts.) > > > > > > > > --------------------------------- > > Do You Yahoo!? > > Yahoo! Shopping - Mother's Day is May 12th!> > CREATE > TABLE T1 ( > > C2 NUMBER, > > C4 NUMBER) ; > > > > CREATE TABLE T2 ( > > C2 NUMBER, > > C3 NUMBER); > > > > CREATE TABLE T3 ( > > C3 NUMBER); > > > > CREATE TABLE T4 ( > > C4 NUMBER); > > > > > > INSERT INTO T1 ( C2, C4 ) VALUES ( 100, 990); > > INSERT INTO T1 ( C2, C4 ) VALUES ( 101, 991); > > INSERT INTO T1 ( C2, C4 ) VALUES ( 102, 992); > > INSERT INTO T1 ( C2, C4 ) VALUES ( 103, 993); > > INSERT INTO T1 ( C2, C4 ) VALUES ( 104, 994); > > > > > > INSERT INTO T2 ( C2, C3 ) VALUES ( 100, 400); > > INSERT INTO T2 ( C2, C3 ) VALUES ( 101, 401); > > INSERT INTO T2 ( C2, C3 ) VALUES ( 102, 402); > > INSERT INTO T2 ( C2, C3 ) VALUES ( 103, 403); > > INSERT INTO T2 ( C2, C3 ) VALUES ( 104, 404); > > > > INSERT INTO T3 ( C3 ) VALUES ( 400); > > INSERT INTO T3 ( C3 ) VALUES ( 401); > > INSERT INTO T3 ( C3 ) VALUES ( 403); > > INSERT INTO T3 ( C3 ) VALUES ( 404); > > > > INSERT INTO T4 ( C4 ) VALUES ( 990); > > INSERT INTO T4 ( C4 ) VALUES ( 991); > > INSERT INTO T4 ( C4 ) VALUES ( 992); > > INSERT INTO T4 ( C4 ) VALUES ( 993); > > > > > __________________________________________________ > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Shopping - Mother's Day is May 12th! > http://shopping.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: sam d > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: S B INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
