my result set is below that is i want to be from my below mention tables i.e
combi_titles has values (x,y,z)
combi_values has (y1,y2,... with self join with x21,x22..).
X(x21..x26),Y(y1..y3) and Z(z1..z6) has fixed in this below result.
selfj has (77,43 with combi_values fk and z1,z2)
please assist to get below result set.
| **combi_titles
** | combi_values | selfj |
|
CREATE TABLE COMBI_TITLE
(
CODE NUMBER, TITLE VARCHAR2(20 BYTE)
)
ALTER TABLE COMBI_TITLE ADD ( CONSTRAINT COMBI_TITLE_PK PRIMARY KEY
(CODE)
);
INSERT INTO COMBI_TITLE ( CODE, TITLE) VALUES ( 1,'y');
INSERT INTO COMBI_TITLE ( CODE, TITLE) VALUES ( 2,'x');
|
CREATE TABLE COMBI_VALUES
( CODE NUMBER, VALUE VARCHAR2(10 BYTE), CODECTFK NUMBER, CODEFK NUMBER
);
ALTER TABLE COMBI_VALUES ADD (
CONSTRAINT COMB_VALUES_PK
PRIMARY KEY
(CODE)
);
ALTER TABLE COMBI_VALUES ADD (
CONSTRAINT COMBI_VALUES_R01
FOREIGN KEY (CODEFK)
REFERENCES COMBI_VALUES (CODE)
ENABLE VALIDATE,
CONSTRAINT COMBI_VALUES_R02
FOREIGN KEY (CODECTFK)
REFERENCES COMBI_TITLE (CODE)
ENABLE VALIDATE);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (1,'y1',1,'');
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (2,'y2',1,'');
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (3,'y3',1,'');
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (4,'x21',2,1);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (5,'x22',2,1);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (6,'x23',2,1);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (7,'x21',2,2);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (8,'x22',2,2);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (9,'x23',2,2);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (10,'x21',2,3);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (11,'x22',2,3);
INSERT INTO COMBI_VALUES ( CODE, VALUE, CODECTFK, CODEFK) VALUES (12,'x23',2,3);
|
CREATE TABLE SELFJ
(
CODE NUMBER, VALUE VARCHAR2(10 BYTE), CODEFK NUMBER, ZVALUE VARCHAR2(10 BYTE)
);
ALTER TABLE SELFJ ADD (
CONSTRAINT SELFJ_PK PRIMARY KEY (CODE) );
ALTER TABLE SELFJ ADD ( CONSTRAINT SELFJ_R01 FOREIGN KEY (CODEFK)
REFERENCES COMBI_VALUES (CODE) ENABLE VALIDATE);
INSERT INTO SELFJ ( CODE, VALUE, CODEFK, ZVALUE) VALUES ( 1,77,4,'z1' );
INSERT INTO SELFJ ( CODE, VALUE, CODEFK, ZVALUE) VALUES ( 2,43,7,'z1' );
INSERT INTO SELFJ ( CODE, VALUE, CODEFK, ZVALUE) VALUES ( 3,45,9,'z3' );
|