CODEWITHSUNDEEP
sqlsql-server
cool breeze
cool breeze

Reputation: 4811

How to check if all of a Teachers students have met a requirement

I have to do the below for every teacher.

Given a TeacherId, I want to select all students who have this Teacher and have passed the first semester. If all the teachers students have passed, I want to call another stored procedure:

EXEC AllTeachersStudentsPassedSemester1 @teacherId

The tables are as follows:

Teacher - teacherId

Students
 -userId
 -teacherId

CourseSummarry
 -userId
 -passedSemester1 (bool)

So to get all users for a given teacher I have:

SELECT userId
FROM Students
WHERE teacherId = @teacherId

How can I test if all the students in that list have passedSemester1 = TRUE, and then call that proc AllTeachersStudentsPassedSemester1 for that teacher. Confused how I can check if all the students have passed.

I then have to somehow loop through all teachers to do this.

This seems like I have to program this in code rather than sql.

Upvotes: 0

Views: 196

Answers (6)

Serpiton
Serpiton

Reputation: 3684

Instead of checking if all the student have passed is easier to check if no student have failed: to check if all the student have passed you need to know how many student there are and how many passed, to check if no one failed you just need to know how many failed.

Select failed = Sum(Cast(passedSemester1 AS Int))
From   CourseSummarry cs
       INNER JOIN Students st ON cs.userId = st.userId
WHERE  @teacherId

As SUM cannot be used on boolean you first need to cast it to another type, in this case integer.

Now if failed is 0 we need to execute the other stored procedure, to do this we can transform failed to a variable and check it with an if

Declare @failed int

Select @failed = Sum(Cast(passedSemester1 AS Int))
From   CourseSummarry cs
       INNER JOIN Students st ON cs.userId = st.userId
WHERE  @teacherId

If @failed = 0 Then
  EXEC AllTeachersStudentsPassedSemester1 @teacherId
End If

The last step is to enclose everything in a stored procedure definition

CREATE Procedure myProc @teacherId
AS
BEGIN
  Declare @failed int

  Select @failed = Sum(Cast(passedSemester1 AS Int))
  From   CourseSummarry cs
         INNER JOIN Students st ON cs.userId = st.userId
  WHERE  @teacherId

  If @failed = 0 Then
    EXEC AllTeachersStudentsPassedSemester1 @teacherId
  End If
END;

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

   SELECT teacherId 
     FROM Students
     JOIN CourseSummarry 
       on CourseSummarry.userId = Students.userId
      and passedSemester1 = 'true' 
 except 
   SELECT teacherId 
     FROM Students
     JOIN CourseSummarry 
       on CourseSummarry.userId = Students.userId
      and passedSemester1 = 'false'

Upvotes: 0

Siyual
Siyual

Reputation: 16917

This query should give you all of the teachers that did not have a failing student:

Select  Distinct S.TeacherId
From    Students            S
Where Not Exists
(
    Select  *
    From    CourseSummary   C
    Where   C.UserId In
    (
        Select  S2.UserId
        From    Students    S2
        Where   S2.TeacherId = S.TeacherId
    )
    And     C.passedSemester1 = 0
)

You can then use this in a CURSOR to iterate over each entry to execute the procedure:

Declare @TeacherId Int

Declare cur Cursor For
Select  Distinct S.TeacherId
From    Students            S
Where Not Exists
(
    Select  *
    From    CourseSummary   C
    Where   C.UserId In
    (
        Select  S2.UserId
        From    Students    S2
        Where   S2.TeacherId = S.TeacherId
    )
    And     C.passedSemester1 = 0
)

Open cur

While (1 = 1)
Begin
    Fetch Next From cur Into @TeacherId

    If @@Fetch_Status <> 0 Break

    Execute AllTeachersStudentsPassedSemester1 @TeacherId 
End

Close cur
Deallocate cur

Upvotes: 1

tale852150
tale852150

Reputation: 1628

I offer this as an aid to solving your issue with "...check if all the students have passed" and not a complete solution to your question.

When you select your Students use the following SQL (this will not be syntactically correct but you'll get the idea):

select userId
from Students join Teacher
  on Students.teacherId = Teacher.teacherId
  and teacherId = @teacherId
  join CourseSummary
  on Students.userId = CourseSummary.userid
  and CourseSummary.passedSemester1 = true;

HTH

Upvotes: 0

Thiago Bittencourt
Thiago Bittencourt

Reputation: 492

Here is your query to get all students that have passed the semester with the given teacher:

SELECT S.userId
FROM Students S, CourseSummarry CS
WHERE CS.passedSemester1 = true
AND CS.userId = S.userId
AND S.teacherId = @teacherId

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

If I understand your question correctly, you can use a join with aggregation to get the list of teachers. Then you would need to use a cursor to call a separate stored procedure passing in each teacher's id. Here is the sql to get the teachers:

select s.teacherid
from students s 
    join coursesummary cs on s.userid = cs.userid
group by s.teacherid
having count(*) = sum(case when cs.passedSemester1 = 'true' then 1 else 0 end)

This presume each student record exists in coursesummary. If that's not the case, you'd need to use an outer join and change the having criteria slightly.

As a side note, something seems off with your table design. I would think you'd need a courseid and a different cross reference lookup table. I won't presume to understand your database though.

Upvotes: 1

Related Questions