CODEWITHSUNDEEP
sqljoininner-join
Lukon
Lukon

Reputation: 265

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

Upvotes: 0

Views: 101

Answers (3)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Could'nt find any issues with your query on a first look.

Still if the SNUM and Year columns are stored as string there may be a chance of space issue in left or right side of the string. Can you trim the column and make a try again.

 SELECT Students.SNUM ,Enrollments.SNUM ,Enrollments.CallNum ,SchClasses .CallNum
   FROM Students 
    INNER JOIN Enrollments 
      ON Students.SNUM = Enrollments.SNUM 
           AND Students.SNUM = '101' 
    LEFT JOIN SchClasses 
       ON LTRIM(RTRIM(Enrollments.CallNum)) = LTRIM(RTRIM(SchClasses.CallNum))
            --AND LTRIM (RTRIM (schClasses.Semester)='sp'
            --AND LTRIM (RTRIM(schClasses.Year))='2013'

Upvotes: 1

Nagahornbill
Nagahornbill

Reputation: 121

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.

Upvotes: 1

user2219063
user2219063

Reputation: 128

The Where Statement is missing. You should write Where instead of the first and.

Upvotes: 0

Related Questions