AnsweredAssumed Answered

How to include no rows(NOT null rows) in an aggregate function?

Question asked by Sanjai sahu on Oct 16, 2018

Hi folks,

I'm writing a query to look for courses that do not have any of its gradable items graded.

In Blackboard when a user doesn't have a grade at all(e.g. no attempt was ever made) there simply isn't a row in the table gradebook_grade

 

So if a course doesn't have any grades at all the gradebook_grade table does not have any rows referencing the primary key of the Blackboard course_id

 

This is what I've used so far:

 

use bblearn

select cm.course_id

from course_main cm

join gradebook_main gbm on cm.pk1 = gbm.crsmain_pk1

join gradebook_grade gbg on gbm.pk1 = gbg.gradebook_main_pk1

where cm.pk1 = 3947

group by cm.course_id

having count(gbg.pk1) <= 0

The course in question(pk1 3947) is confirmed to not have any grades. So SQL Server says 0 rows affected, naturally.

 

The thing is, it doesn't select the course_id. I'm guessing having doesn't account for blank/non-existent rows. Is there a way to structure the query to get the course ID when there isn't anything returned? Am I joining on the wrong columns or using where on the wrong column?

 

Any help would be appreciated!

 

Thank you

Sanjai Sahu

Developer

Mindmajix Technologies

Outcomes