none
Count Records in Table Except If Related Record Exists in Child Table

    Question

  • Dear All

    I have the following query that counts all of the records in the table SENAlert based upon the teacher's username in a related table.

    SELECT COUNT(SENAlert.SENAlertID) AS Expr1 FROM Class INNER JOIN ClassMember ON Class.ClassClassCode = ClassMember.ClassMemberClassCode
    INNER JOIN Student ON ClassMember.ClassMemberStudentID = Student.StudentID
    INNER JOIN SENAlert ON Student.StudentID = SENAlert.SENAlertStudentID
    INNER JOIN Teacher ON Class.ClassTeacherCode = Teacher.TeacherCode WHERE (Teacher.TeacherUsername = 'dsmith')

    I need to extend this query by adding another table called SENAlertHistory. I would like to count the number of alerts (SENAlertID) for the specified teacher but where there is no related record in the SENAlertHistory table. Here's what the relationship diagram looks like:

    What's going to happen is when a teacher clicks a button to say they have read an alert, I will record this in the SENAlertHistory table. Therefore when performing a count of how many unread alerts the teacher has, I need to ignore the alerts they have already read (i.e. the records in the SENAlertHistory table).

    This is a bit too advanced for me -- I have tried! I was hoping someone would be able to help me please?

    Many thanks

    Daniel

    Saturday, June 21, 2014 3:39 PM

Answers

  • Hi,

    and welcome to the forums. Depending on the selectivity this could be a good solution in your case:

    SELECT COUNT(SENAlert.SENAlertID) AS Expr1
    FROM Class INNER JOIN ClassMember ON Class.ClassClassCode = ClassMember.ClassMemberClassCode
    INNER JOIN Student ON ClassMember.ClassMemberStudentID = Student.StudentID
    INNER JOIN SENAlert ON Student.StudentID = SENAlert.SENAlertStudentID
    INNER JOIN Teacher ON Class.ClassTeacherCode = Teacher.TeacherCode
    WHERE (Teacher.TeacherUsername = 'dsmith')
    AND NOT EXISTS 
    (
    	SELECT * FROM SenAlertHistory SAH
    	WHERE Teacher.TeacherCode = SAH.SenAlertHistoryTeacherCode
    )
    

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Saturday, June 21, 2014 3:54 PM

All replies

  • Hi,

    and welcome to the forums. Depending on the selectivity this could be a good solution in your case:

    SELECT COUNT(SENAlert.SENAlertID) AS Expr1
    FROM Class INNER JOIN ClassMember ON Class.ClassClassCode = ClassMember.ClassMemberClassCode
    INNER JOIN Student ON ClassMember.ClassMemberStudentID = Student.StudentID
    INNER JOIN SENAlert ON Student.StudentID = SENAlert.SENAlertStudentID
    INNER JOIN Teacher ON Class.ClassTeacherCode = Teacher.TeacherCode
    WHERE (Teacher.TeacherUsername = 'dsmith')
    AND NOT EXISTS 
    (
    	SELECT * FROM SenAlertHistory SAH
    	WHERE Teacher.TeacherCode = SAH.SenAlertHistoryTeacherCode
    )
    

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Saturday, June 21, 2014 3:54 PM
  • Try below code

    -- If you dont need the read alert count
    SELECT COUNT(SENAlert.SENAlertID) AS Expr1
    FROM Class INNER JOIN ClassMember ON Class.ClassClassCode = ClassMember.ClassMemberClassCode
    INNER JOIN Student ON ClassMember.ClassMemberStudentID = Student.StudentID
    INNER JOIN SENAlert ON Student.StudentID = SENAlert.SENAlertStudentID
    INNER JOIN Teacher ON Class.ClassTeacherCode = Teacher.TeacherCode
    WHERE (Teacher.TeacherUsername = 'dsmith')
    AND NOT EXISTS 
    (SELECT * FROM SenAlertHistory SAH WHERE Teacher.TeacherCode = SAH.SenAlertHistoryTeacherCode and SENAlert.SENAlertID = SAH.SEMAlertHistorySENAlertID )
    
    -- If you need the read alert count
    
    SELECT COUNT(SENAlert.SENAlertID) - COUNT(SAH.SEMAlertHistorySENAlertID) AS Expr1,COUNT(SAH.SEMAlertHistorySENAlertID)readalert
    FROM Class INNER JOIN ClassMember ON Class.ClassClassCode = ClassMember.ClassMemberClassCode
    INNER JOIN Student ON ClassMember.ClassMemberStudentID = Student.StudentID
    INNER JOIN SENAlert ON Student.StudentID = SENAlert.SENAlertStudentID
    INNER JOIN Teacher ON Class.ClassTeacherCode = Teacher.TeacherCode
    LEFT OUTER JOIN SenAlertHistory SAH ON Teacher.TeacherCode = SAH.SenAlertHistoryTeacherCode and SENAlert.SENAlertID = SAH.SEMAlertHistorySENAlertID 
    WHERE (Teacher.TeacherUsername = 'dsmith')

    Thanks

    Saravana Kumar C

    Saturday, June 21, 2014 5:25 PM