RE: SQL -Can this be done in a single query

From: sam d <sam_orafan_at_yahoo.com>
Date: Thu, 09 May 2002 01:53:18 -0800
Message-ID: <F001.0045C68B.20020509015318@fatcity.com>

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).

Received on Thu May 09 2002 - 04:53:18 CDT