I have these two tables:
COURSE and 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.
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;