How to get a list of names + a count of occurrences relating to the name from another table
- by Jacob Degeling
Hi,
Hope my subject line was descriptive enough.
Basically, I have a table called Students, and one called Absences. The Students table contains student information (Name, DOB, StudentID etc.), the Absences table contains any absences (Date, Reason, related by StudentID). I want to get a full list of the students, plus a count of their absences.
Sample results would be:
StudentName Absences
------------- -----------
Johnny 3
Mark 2
James 1
Anthony 0
Sharon 0
Can someone suggest a way I should tackle this problem?
I would prefer to have a single SQL statement, possibly incorporating some views etc.
Thanks,
Jacob.