I have 3 tables in my SQL Server I am wondering why is my select statement not working or giving the result with my condition in the where clause. I learned back there to use this kind of method..

Subject table
SubjectID      CourseCode
   6            SubjectA
   7            SubjectB
   8            SubjectC

Schedule table
ScheduleID     Room
   15          Room1
   16          Room2
   17          Room3

AssignSchedule table
AssignScheduleID       ScheduleID       SubjectID
    14                     15               6
    15                     16               7

I want to get the room of the subject SubjectA. In order to get that I came up with this select statement query

Select Subject.CourseCode, 
Schedule.Room from Subject, 
Schedule, AssignSchedule 
where 
AssignSchedule.ScheduleID = (Select Schedule.Schedule.ID 
from Schedule where Room = 'Room1') 
AND AssignSchedule.SubjectID = (Select Subject.SubjectID from Subject where CourseCode = 'SubjectA');

What happens is like this

CourseCode      Room

  SubjectA      Room1
  SubjectB      Room1
  SubjectC      Room1
  SubjectA      Room2
  SubjectB      Room2
  SubjectC      Room2
  SubjectA      Room3
  SubjectB      Room3
  SubjectC      Room3

My expected output would be like this

CourseCode     Room
SubjectA       Room1

What is wrong? Help guys..

up vote 4 down vote accepted

The WHERE clause returns the desired single row AssignSchedule but you have no correlation between that row and the outer Subject and Schedule tables so you get a Cartesian product of all Subject and Schedule combinations. You could add the outer table correlations to your existing query to get the desired result:

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE   AssignSchedule.ScheduleID = ( SELECT    Schedule.ScheduleID
                                      FROM      Schedule
                                      WHERE     Room = 'Room1'
                                    )
        AND AssignSchedule.SubjectID = ( SELECT Subject.SubjectID
                                         FROM   Subject
                                         WHERE  CourseCode = 'SubjectA'
                                       )
        AND AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID;

A better approach would be use simple equality predicates instead in the ugly subqueries. Below are examples using the old ANSI SQL-89 syntax you've been using as well as the ANSI SQL-92 equivalent. I suggest the latter as it helps avoid missing join predicates.

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
      , Schedule
      , AssignSchedule
WHERE
            AssignSchedule.ScheduleID = Schedule.ScheduleID
        AND AssignSchedule.SubjectID = Subject.SubjectID
        AND Schedule.Room = 'Room1'
        AND Subject.CourseCode = 'SubjectA';

SELECT  Subject.CourseCode
      , Schedule.Room
FROM    Subject
JOIN AssignSchedule ON AssignSchedule.SubjectID = Subject.SubjectID
JOIN Schedule ON AssignSchedule.ScheduleID = Schedule.ScheduleID
WHERE   
        Schedule.Room = 'Room1'
    AND Subject.CourseCode = 'SubjectA';
  • I didn't know that I had to add the outer table correlations! Wow! I forgot the use of join but it is must simple and organized. Should the outer correlations really needed when performing queries with single/multiple table/s? Thank you so much! – user3323654 May 3 '15 at 14:00
  • @user3323654, the concept of outer correlation applies only when multiple tables are involved (including the same table specified multiple times). In the context of the outer FROM clause, the rows in the tables are usually related to one another such that relationship needs to be specified in ON or WHERE clause. – Dan Guzman May 3 '15 at 14:12
  • Ohh I see.. Thank you again for this one! – user3323654 May 3 '15 at 14:17

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.