Re: How to write this QUERY - New

Maybe like this :

SELECT DECODE (ida1, NULL, DECODE (ida2, NULL, ida3, ida2), ida1) ida,

       DECODE (ta1, NULL, DECODE (ta2, NULL, ta3, ida2), ta1) ta, idc, priority   FROM c, b, a1, a2, a3
 WHERE c.fkc_b = b.idb

   AND a1.fka1_b(+) = b.idb
   AND a2.fka2_b(+) = b.idb
   AND a3.fka3_b(+) = b.idb

hth

Chris
( ;-) very easy to try when you put the ddl script)

nougain_at_hotmail.com (javaq) wrote in message news:<e9af93aa.0201022020.7be9b154_at_posting.google.com>...
> I have following five tables in three layes ...
> ----------------------------------------------
> A1 \
> A2 - B - C
> A3 /
>
> CREATE TABLE B (IDB NUMBER PRIMARY KEY, STATE NUMBER);
>
> CREATE TABLE A1 (IDA1 NUMBER PRIMARY KEY, FKA1_B NUMBER REFERENCES B
> UNIQUE, TA1 VARCHAR2(100));
> CREATE TABLE A2 (IDA2 NUMBER PRIMARY KEY, FKA2_B NUMBER REFERENCES B
> UNIQUE, TA2 VARCHAR2(100));
> CREATE TABLE A3 (IDA3 NUMBER PRIMARY KEY, FKA3_B NUMBER REFERENCES B
> UNIQUE, TA3 VARCHAR2(100));
>
> CREATE TABLE C (IDC NUMBER PRIMARY KEY, FKC_B NUMBER REFERENCES B,
> PRIORITY VARCHAR2(100));
>
> I am inserting some sample data...
> ----------------------------------
> INSERT INTO B VALUES (1, 111);
> INSERT INTO B VALUES (2, 111);
> INSERT INTO B VALUES (3, 222);
> INSERT INTO B VALUES (4, 222);
> INSERT INTO B VALUES (5, 222);
> INSERT INTO B VALUES (6, 111);
> INSERT INTO B VALUES (7, 111);
>
> INSERT INTO A1 VALUES (1, 1, 'Hello world A1_1');
> INSERT INTO A1 VALUES (2, 4, 'Hello world A1_2');
> INSERT INTO A1 VALUES (3, 5, 'Hello world A1_3');
> INSERT INTO A1 VALUES (4, null, 'Hello world A1_4');
> INSERT INTO A2 VALUES (1, 2, 'Hello world A2_1');
> INSERT INTO A2 VALUES (2, 6, 'Hello world A2_2');
> INSERT INTO A2 VALUES (3, null, 'Hello world A2_3');
> INSERT INTO A2 VALUES (4, null, 'Hello world A2_4');
> INSERT INTO A3 VALUES (1, 3, 'Hello world A3_1');
> INSERT INTO A3 VALUES (2, null, 'Hello world A3_2');
> INSERT INTO A3 VALUES (3, 7, 'Hello world A3_3');
>
> INSERT INTO C VALUES (1, 1, 'Priority 1');
> INSERT INTO C VALUES (2, 2, 'Priority 2');
> INSERT INTO C VALUES (3, 5, 'Priority 3');
> INSERT INTO C VALUES (4, 7, 'Priority 4');
>
> Table A1, A2, A3 stores an id of table B record in it. It is
> gaurenteed that one ID
> of table B will be refered by ONLY one out of A1, A2, A3. Table C
> stores
> the reference of table B
>
> I want to write a single query that gives me...
> -----------------------------------------------
> IDC, PRIORITY FROM C
> IDB, STATE From B
> IDA1 or IDA2 or IDA3, TA1 or TA2 or TA3 from A1 or A2 or A3 based on
> the B.IDB value = Ax.FKAx_B value
>
> Regards
Received on Fri Jan 04 2002 - 21:48:29 CET