Why are these queries are returning different results?

hello query sql queries mere mortals chapter 13:

list each staff member , count of classes each scheduled teach.

i did query in 3 ways:

select stf.stffirstname, stf.stflastname, count(c.classid)

from (staff stf inner join faculty_subjects fs on stf.staffid = fs.staffid)

                        inner join classes c on c.subjectid = fs.subjectid

                        group by stf.stflastname, stf.stffirstname

output 23 records.

==========================================

tried joins in different way , count classid in different table

select stf.stffirstname, stf.stflastname, count(fc.classid) as countofclassestaught

from staff stf inner join faculty_classes fc

on stf.staffid = fc.staffid

group by stf.stffirstname, stf.stflastname

output 24 records

================================================

author asks query subquery

 

select stf.stffirstname, stf.stflastname, (select count(fc.classid) from faculty_classes fc

                                          where fc.staffid = stf.staffid )

from staff stf   

output 27 records,

then author asks why 4 records more?

how answer these discrepancies?

i appreciate help,

gggggnnnnn

---

gggggnnnnn

there may duplicate records in faculty_classes table staff id , class id. try below uery , post result. 

select stf.stffirstname, stf.stflastname, 

(select count( distinct fc.classid) fromfaculty_classes fc

                                          where fc.staffid = stf.staffid )

from staff stf 

SQL Server  >  SQL Server Database Engine