I have a Dogs table, a Kennels table and Visits table that contains DogId and KennelId columns.
I am trying to get a full list of all the dogs, with a column showing the number of visits to a particular kennel, so many of the results will contain a 0 as the visit count.
This is what I’ve tried:
select dog.*, visits.visitCount FROM (select * from Dogs) as dog, (select COUNT (Visits.Id) as visitCount from Visits INNER JOIN Dogs ON Dogs.Id = Visits.DogId where KennelId = 'E15A8C60-E0FE-472D-9CC4-08DA251A992F') as visits
With this statement, I end up with all of the dogs, but with the same visit count for all, which is incorrect. I assume my count function is simply executed once with the result repeated for the remaining rows. I do not know how to correct this. Any help will be much appreciated!
With no table schemas or sample data, a guess would be something like the following:
select d.*, Coalesce(v.VisitCount,0) VisitCount from dogs d left join ( select DogId, Count(*) VisitCount from visits v where v.KennedId = 'E15A8C60-E0FE-472D-9CC4-08DA251A992F' group by DogId )v on v.DogId = d.DogId;