I have four tables in a database: City, User, CityRating, CityGreeting. The CityRating table has the UserID and CityID as the PK, and those are FKs to the USer and City table. The CityGreeting table has no PK, but has the UserID and CityID as FKs (the idea is that a user can greet a city as many times as desired, but only rate a city once).
I am trying to write a query that will return the average rating of the city overall, as well as the times a specific user greeted the city:
select City.CityID, City.CityName, City.CityStateOrProvince,
ROUND(AVG(Cast(RateCity.Rating as float)), 2) as AverageRating,
(select COUNT(HelloCity.CityID) from HelloCity where HelloCity.UserID like '<guid>') as TimesVisited
right join RateCity
on City.CityID = RateCity.CityID
right join HelloCity
on City.CityID = HelloCity.CityID
group by City.CityID, City.CityName,
City.CityStateOrProvince, City.CityCountry, City.CityImageUri
I believe you need to aggregate the tables, apart from
city separately for this to work correctly:
select c.*, rc.AverageRating, coalesce(hc.TimesVisited, 0) as TimesVisited from City c join (select CityId, ROUND(AVG(Cast(RateCity.Rating as float)), 2) as AverageRating from RateCity rc group by CityId ) rc on c.CityID = rc.CityID left join (select CityId, count(*) as TimesVisited from HelloCity hc where hc.UserID like '<guid>' group by CityId ) hc on c.CityId = hc.CityId;
right join. That would imply that there are
CityIds in the other two tables that are not in
left joinfor the
HelloCitytable, because not all cities might have visitors.
left joinfor the
RateCitytable as well, if not all cities have ratings.