Get rows from primary table, and also a count of how many times that record appears in a secondary t-CodePudding

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!

CodePudding user response：

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;