sql - Query to display student_no, prj_name and prj_dur

Question

I have following table

        TBL_STUDENT (STUDENT_NO VARCHAR2(3) PRIMARY KEY STUDENT_NAME VARCHAR2 (10) STUDENT_DOB DATE STUDENT_DOJ DATE)

        TBL_PROJECT (PRJ_NO VARCHAR2 (3) PRIMARY KEY PRJ_NAME VARCHAR2 (15) PRJ_DUR NUMBER (2) PRJ_PLATFORM VARCHAR2 (10))

       TBL_STUDENTPROJECT (STUDENT_NO VARCHAR2 (3) PRJ_NO VARCHAR2 (3) DESIGNATION VARCHAR2 (10) PRIMARYKEY (STUDENT_NO,PRJ_NO,DESIGNATION) FOREIGN KEY(STUDENT_NO) of TBL_STUDENT FOREIGN KEY(PRJ_NO) of TBL_PROJECT) 

I want to display student_no, prj_name and prj_dur;

        select p.prj_name, s.student_no, p.prj_dur from tbl_project p inner join on tbl_studentproject s where p.prj_no = s.prj_no

标签： sqloracleoracle11g

Answer 1

您需要进行内连接才能找到 prj_name、student_no 和 prj_dur。

SELECT p.PRJ_NAME, s.STUDENT_NO, p.PRJ_DUR 
     from TBL_STUDENTPROJECT sp 
     INNER JOIN TBL_STUDENT s on sp.STUDENT_NO = s.STUDENT_NO 
     INNER JOIN TBL_PROJECT p ON sp.PRJ_NO = p.PRJ_NO;