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:bhulubhuli_at_yahoo.com]
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 <sam_orafan_at_yahoo.com> 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 <sam_orafan_at_yahoo.com> 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: sam_orafan_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: bhulubhuli_at_yahoo.com
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: ListGuru_at_fatcity.com (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: sam_orafan_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed May 15 2002 - 01:18:19 CDT
