SQL Query Using Multiple Table Joins

Question

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:

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

Is there a reason you're putting all your conditions on the join statements instead of a WHERE clause? Unless there's a specific reason for that, a WHERE clause is probably what you're missing. — , Sep 07 '16 at 04:00
Hi Phillip. No reason in particular, I just saw a similar problem done this way and I was hoping for positive results. A previous poster also recommended I change the first condition to 'where', however it's returning the same result: no rows selected. — Lukon, Sep 07 '16 at 04:03
You provide 2 additional tables in your schema but don't show them in your query. As is, I would suspect your query to return a single result -- Intro to MIS. Are you perhaps joining on `PreReq` where `cnum` 300 doesn't exist? — sgeddes, Sep 07 '16 at 04:04
This is how i debug multiple joins.. First of all I will add relevant conditions in the associated join. So my query will be something like : ---- 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' ----- Just start with one join and check the results, then add another and then the last one. You will find which join is causing issue and then fix it. — Nagahornbill, Sep 07 '16 at 04:07
Replacing the INNER JOIN with a LEFT JOIN might be revealing — kjmerf, Sep 07 '16 at 04:08
The easiest way to figure out the problem is just run the query one table at a time. Select from students looking for that student, if you get the expected result, add the join to enrollments, etc., until you don't have the results you expect. — , Sep 07 '16 at 04:16
I was able to select from students joining enrollments and got back two records, which was correct. However, once I attempted to then join the SchClasses table, I'm getting the same error: no rows selected. Any idea? Code is as follows: 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'; spool off — Lukon, Sep 07 '16 at 04:38

Unnikrishnan R · Answer 1 · 2016-09-07T05:11:54.367

1

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'

edited Sep 07 '16 at 05:11

answered Sep 07 '16 at 04:07

Unnikrishnan R

4,965
1
12
21

Thanks for the suggestion, however this did not return anything different. Same result: no rows selected. – Lukon Sep 07 '16 at 04:15
Change your inner join with left join and try it again.. And let me know the result that you are getting – Unnikrishnan R Sep 07 '16 at 04:20
I would also suggest removing the AND clauses at the end for now, to see if the issue is with the joins or with the particular record – kjmerf Sep 07 '16 at 04:21
what is the data type of CallNum column ? try with the updated query and let me know what you are getting.. – Unnikrishnan R Sep 07 '16 at 04:51
This is returning two records, but the Semester, Department & Course Number columns are all blank. – Lukon Sep 07 '16 at 05:01
can you comment the AND conditions and let me know what you are getting.. just try with the updated script.. i wanted to know what you are getting in Enrollments.CallNum and SchClasses .CallNum. – Unnikrishnan R Sep 07 '16 at 05:11

score 1 · Accepted Answer · answered Sep 07 '16 at 04:27

1

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.

answered Sep 07 '16 at 04:27

Nagahornbill

121
7

I tried something like this earlier, logically it makes sense to me but for whatever reason the query is returning the same value: no rows selected It appears the code breaks @ the second inner join: 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'; spool off – Lukon Sep 07 '16 at 04:35
Start with one condition in that particular join and see when it stops giving u result i.e there are 3 conditions in that join add one by one and check the results.. – Nagahornbill Sep 07 '16 at 04:37
Doesn't look like the second join is giving the correct results. If you look at the results, it's only returning two records.. and they are both for Andy for the Fall Semester, shouldn't one be at least Spring? I don't understand how that result is being queried base off of the code.. – Lukon Sep 07 '16 at 04:55
So as far as I understand, you don't get any results when you have " SchClasses.Semester = 'Sp' " condition in your join.. Try " LTRIM (RTRIM (schClasses.Semester)='Sp' " as mentioned by Unnikrishnan R and check the results.. – Nagahornbill Sep 07 '16 at 05:02
I have a sample schema and query which you can execute here : [link] (http://sqlfiddle.com/#!7/54385/1).. Change the schema and query as per your requirement and i will check whats the issue if the query is not working.. – Nagahornbill Sep 07 '16 at 05:09
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/122795/discussion-between-surge-point-and-nagahornbill). – Lukon Sep 07 '16 at 05:20

score 0 · Answer 3 · answered Sep 07 '16 at 03:59

0

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

answered Sep 07 '16 at 03:59

user2219063

128
6

Hmm, I just updated my first condition to 'where' and it returned the same query: no rows selected. – Lukon Sep 07 '16 at 04:01
With `inner join`, they are equivalent: http://stackoverflow.com/a/354095/1073631 – sgeddes Sep 07 '16 at 04:05

SQL Query Using Multiple Table Joins

3 Answers3