Home -> Usenet -> comp.databases.oracle -> How to write this QUERY - New

How to write this QUERY - New

From: javaq <nougain_at_hotmail.com>
Date: 2 Jan 2002 20:20:40 -0800
Message-ID: <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 Thu Jan 03 2002 - 05:20:40 CET












Original text of this message