CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Hammam Muhareb
Hammam Muhareb

Reputation: 355

A stored procedure to select from three tables

I have these three tables

Courses

course ID     course Name     course start time   course end time
1             Math            10:00:00            11:00:00
2             Science         11:00:00            12:00:00
3             geography       09:00:00            10:00:00

student_courses

course ID     student ID      Final cost
1             2               100
2             3               200

Students

student ID     student name
1              AAAA
2              BBBB

I need a select statement to place in a stored procedure to only return [Course ID], [Course name] column values of the courses that a student is not enrolled in (achieved)

AND check the student current courses' start and end times and only return courses in which times the student is available (doesn't have a class)

my select statement that only return courses that the student is not enrolled in::

SELECT [Course ID], [Course name] 
FROM Courses
WHERE [Course ID] NOT IN 
 (SELECT Course ID from student_courses WHERE [student ID]=1)

How can i edit this statement to include the available time condition (knowing i want to put it in a stored procedure)....

Upvotes: 0

Views: 692

Answers (1)

fancyPants
fancyPants

Reputation: 51868

This is the query you need:

SELECT [Course ID], [Course name] 
FROM Courses 
WHERE [Course ID] NOT IN 
 (SELECT Course ID from student_courses sc
  INNER JOIN Courses c ON sc.[course ID] = c.[course ID] 
  WHERE [student ID] = 1
  AND (c.[course start time] BETWEEN Courses.[course start time] AND Courses.[course end time]
  OR c.[course end time] BETWEEN Courses.[course start time] AND Courses.[course end time]))

I see no reason, why this should be in a stored procedure. A view might be reasonable.

Read more about views here.

Upvotes: 1

Related Questions