Surge Point Surge Point - 2 months ago 9
SQL Question

SQL Query Using Multiple Table Joins

I have been beating my head against the wall for the past few hours (embarassing, I know) but I just can't seem to get this query to work! I have looked at other similar questions, seen a few tutorials on Youtube, but for whatever reason I cannot get my code to work. So, alas here I am:

The query is supposed to return three things: DEPT (Department), CNUM (Course Number) & CTITLE (Course Title) of a student's ID who took courses during any given semester.

For example, I'm trying to get my query to state the course department, title and number of an individual who's ID is 101 and took courses in Spring 2013. Below is my code:

set echo on

spool c:\is\program1.txt

SELECT Courses.DEPT, Courses.CNUM, Courses.CTITLE
FROM Students
INNER JOIN Enrollments
ON Students.SNUM = Enrollments.SNUM
INNER JOIN SchClasses
ON Enrollments.CallNum = SchClasses.Callnum
INNER JOIN Courses
ON SchClasses.CNUM = Courses.CNUM
AND Students.SNUM = '101'
AND SchClasses.Semester = 'Sp'
AND SchClasses.Year = '2013';

spool off


My Table Data is shown below:
enter image description here

The following query is returning: no rows selected

I am NOT looking for someone to do my work for me, I'm just looking for a point in the right direction. Any advice would be greatly appreciated, thank you kindly!




I have solved this problem with the help of you all, thank you very much. It turns out I was logically thinking about this the wrong way -- there were actually no values inserted into the Spring record and thus the result of no rows being found is correct!

It was such a simple oversight but I have figured it out. Thank you all very much; the correct solution remains as follows:

set echo on

spool c:\is\program1.txt

SELECT Courses.DEPT, Courses.CNUM, Courses.CTITLE
FROM Students
INNER JOIN Enrollments
ON Students.SNUM = Enrollments.SNUM
INNER JOIN SchClasses
ON Enrollments.CallNum = SchClasses.Callnum
INNER JOIN Courses
ON SchClasses.CNUM = Courses.CNUM
AND Students.SNUM = '101'
AND SchClasses.Semester = 'Sp'
AND SchClasses.Year = '2013';

spool off

Answer
set echo on

spool c:\is\program1.txt

SELECT *
FROM Students
    INNER JOIN Enrollments 
       ON Students.SNUM = Enrollments.SNUM AND Students.SNUM = '101'
    --INNER JOIN SchClasses  
    --   ON Enrollments.CallNum = SchClasses.Callnum AND SchClasses.Semester 'Sp' AND SchClasses.Year = '2013';
    -- INNER JOIN Courses     ON SchClasses.CNUM = Courses.CNUM

Run this query. If you get results, uncomment the next inner join. If you don't get results, debug why your conditions are not satisfied. If you get the result uncomment the next join. Hope it helps.

Comments