Query return 0 for null values

[rachello89]

I have two tables: tblStudents and tblEnrollments. The query I have designed shows the StudentID and counts the number of Enrollments that each student has. I want the students who don't have any enrollments to still show up and have a 0 by their ID. Right now, only the students with enrollments show up in the query results.



Here is my SQL Statement:

SELECT tblStudents.StudentID, Count(tblEnrollments.EnrollmentID) AS CountOfEnrollmentID
FROM tblStudents INNER JOIN tblEnrollments ON tblStudents.StudentID = tblEnrollments.StudentID
GROUP BY tblStudents.StudentID;

What do I need to do to it to have null values display as zero? Thanks!

[RuralGuy]

You are probably going to want a Left Join and use the Nz() function.

[rachello89]

Thanks, that did it! I was trying to use the Nz() function before, but was unsuccessful. Changing to LEFT JOIN fixed it. BTW, what is the difference between LEFT JOIN and INNER JOIN?

[RuralGuy]

Left Join returns *all* of the records from the "Left" table and records from the "Right" table where the criteria matches. Inner Join *only* returns records where the criteria matches from both tables.

[rachello89]

Thank you!