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..