AnsweredAssumed Answered

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

Question asked by Sanjai sahu on Oct 16, 2018
Latest reply on Dec 19, 2018 by wt0069224

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