SQL -Can this be done in a single query

From: sam d <sam_orafan_at_yahoo.com>
Date: Wed, 08 May 2002 20:53:20 -0800
Message-ID: <F001.0045C5A6.20020508205320@fatcity.com>

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

-- 
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 08 2002 - 23:53:20 CDT