5 Replies Latest reply on Jan 27, 2013 5:30 PM by christinesephton

    Having Count() = 0??

    jbrown

      How does one find the records from table 1 that have a count of 0 in table two?

      I used this sql statement:

       

      ExecuteSQL("

      SELECT k.FirstLastName

      FROM A_KIPPsters AS k

      JOIN BehaviorTable AS b

      ON k.Apk_StudentID = b.fk_StudentID

      WHERE k.GradeNo = ?

      AND b.EventType LIKE ?

      And b.SchoolYear = ?

      AND b.MarkingPeriod = ?

      GROUP BY k.FirstLastName, k.LastName

      HAVING COUNT(b.EventType) = 1

      ORDER BY k.LastName";"";"";5;"%CP";2012;2)

       

      I'm sure people will find better ways to do this, and I welcome those, but I wonder why the HAVING COUNT(b.EventType) = 0 doesn't work. Is it because this there is nothing returned?

       

      How do you find a count of 0 from table 2

       

      Thanks

      jb