sql - 如何从具有多个外键的两个不同表中进行选择

Question

首先使用图像解释我需要什么:

第二个创建表:

CREATE SEQUENCE  SEQ_tab7  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
CREATE SEQUENCE  SEQ_tab8  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

CREATE TABLE TABLE_7 
(
     ID NUMBER(19,0) DEFAULT SEQ_tab7.nextval  NOT NULL
    ,DESCRIPTION    VARCHAR2(256) NOT NULL
    ,CONSTRAINT TAB_7_PK PRIMARY KEY (ID) ENABLE
);

CREATE TABLE TABLE_8 
(
     ID NUMBER(19,0) DEFAULT SEQ_tab8.nextval  NOT NULL
    ,FIELD_1    NUMBER(19,0) NOT NULL
    ,FIELD_2    NUMBER(19,0) NOT NULL
    ,FIELD_3    NUMBER(19,0) NOT NULL
    ,FIELD_4    VARCHAR2(256)
    ,CONSTRAINT TAB_8_PK PRIMARY KEY (ID) ENABLE
    ,CONSTRAINT tab_8_FIELD_1_FK FOREIGN KEY (FIELD_1) REFERENCES TABLE_7(ID) 
    ,CONSTRAINT tab_8_FIELD_2_FK FOREIGN KEY (FIELD_2) REFERENCES TABLE_7(ID) 
    ,CONSTRAINT tab_8_FIELD_3_FK FOREIGN KEY (FIELD_3) REFERENCES TABLE_7(ID) 
);

第三 - 插入:

Insert into TABLE_7 (ID,DESCRIPTION) values ('1','desc_1');
Insert into TABLE_7 (ID,DESCRIPTION) values ('2','desc_2');
Insert into TABLE_7 (ID,DESCRIPTION) values ('3','desc_3');


Insert into TABLE_8 (ID,FIELD_1,FIELD_2,FIELD_3,FIELD_4) values ('23','1','2','1','lorem_1');
Insert into TABLE_8 (ID,FIELD_1,FIELD_2,FIELD_3,FIELD_4) values ('43','1','3','3','lorem_2');
Insert into TABLE_8 (ID,FIELD_1,FIELD_2,FIELD_3,FIELD_4) values ('54','3','3','3','lorem_3');

怎样才能得到想要的结果？

致以诚挚的问候

Answer 1

使用多个联接

试试这个:

SELECT Base.ID, FirstJoin.Desc, SecondJoin.Desc, ThirdJoin.Desc, Base.field_4
FROM table_2 Base
JOIN table_1 FirstJoin ON FirstJoin.ID = Base.Field_1
JOIN table_1 SecondJoin ON SecondJoin.ID = Base.Field_2
JOIN table_1 ThirdJoin ON ThirdJoin.ID = Base.Field_3