Joining two tables checking values of one table to display result of the second table

I have these two tables:
COURSE and COURSE_SEQUENCE_REPORT

COURSE

COURSE_SEQUENCE_REPORT

I am trying to display course number, course name, course prerequisite number and course prerequisite name. The prerequisite ID is a is obtained as a relation to the course ID. If the presequisite ID is 11 then the prerequisite name is Mathematics from the course ID. I was able to come up with this:

SELECT C.COURSE_NUMBER AS "COURSE NUMBER", 
C.COURSE_NAME AS "COURSE NAME"
FROM COURSE C JOIN COURSE_SEQUENCE_REPORT CSR
ON CSR.COURSE_ID = C.COURSE_ID
ORDER BY C.COURSE_NUMBER;

…but I am left with the course prerequisite number and name. How do I go about it please? I am confused.

1 Answer

You want two joins:

SELECT C.COURSE_NUMBER AS COURSE_NUMBER, 
       C.COURSE_NAME AS COURSE_NAME,
       CP.COURSE_NUMBER AS PREREQUISITE_NUMBER, 
       CP.COURSE_NAME AS PREREQUISITE_NAME       
FROM COURSE_SEQUENCE_REPORT CSR JOIN
     COURSE C
     ON CSR.COURSE_ID = C.COURSE_ID JOIN
     COURSE CP
     ON CSR.PREREQUISITE_ID = CP.COURSE_ID
ORDER BY C.COURSE_NUMBER;

Archive from: https://stackoverflow.com/questions/59018949/joining-two-tables-checking-values-of-one-table-to-display-result-of-the-second

Share

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *