Why is this select statement not returning what I've provided? 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..
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';