How many students enrolled in a subject

[Sandy]

First of all, this isn't 'homework'. I'm trying to set up a simple
database for my own purposes but I'll modify the tables into terms
that make it simple.

Imagine we have the following tables:

student (studentName, &c)
subject (subjectName, &c)
student_subject (studentId, subjectId)

Now one query I want to perform is to see a full list of students and one
of the columns needs to be the number of subjects they're enrolled in.

I've been able to do this by including all three tables in query. Grouped
by studentName and Count function for subjectId. This gives me a table
which shows me a list of students and the number of subjects they are
enrolled in.

However, I also need to see students that are enrolled in no classes (ie.
show '0' for those students). But because the query only finds records
where there is a relationship between student and subject it doesn't find
them.

So how can I go about doing this?

 

[Marshall Barton]

First of all, this isn't 'homework'. I'm trying to set up a simple
database for my own purposes but I'll modify the tables into terms
that make it simple.

Imagine we have the following tables:

student (studentName, &c)
subject (subjectName, &c)
student_subject (studentId, subjectId)

Now one query I want to perform is to see a full list of students and one
of the columns needs to be the number of subjects they're enrolled in.

I've been able to do this by including all three tables in query. Grouped
by studentName and Count function for subjectId. This gives me a table
which shows me a list of students and the number of subjects they are
enrolled in.

However, I also need to see students that are enrolled in no classes (ie.
show '0' for those students). But because the query only finds records
where there is a relationship between student and subject it doesn't find
them.

I think all you need to do is change the Inner Join to an
outer join from the student table to the student_subject
table. In this situation, I don't see a reason to include
the subject table.

SELECT student.studentName, ...
Count(student_subject.subjectId) As NumSubjects
FROM student LEFT JOIN student_subject
ON student.studentId = student_subject.subjectId