how to use sub query in sql? : i have 4 tables :
Travelers (TravelerID,FirstName,LastName)
Guides(GuideID,FirstName,LastName)
Locations(LocationID,LocationName)
Trips(TravelerID,GuideID,LocationID,Stars,StartDate,ReturnDate)

I want to return per guide the name of location which he guided the maximum number of  travelers .

i have tried to use this sub query but it doesn't work :

SELECT G.FirstName,L.LocationName,count(distinct(TravelerID))as number_of_travelers_per_guide 
FROM Guides AS G LEFT JOIN Trips AS T USING (GuideID) LEFT JOIN Locations AS L USING (LocationID)
GROUP BY G.FirstName,L.LocationName 
HAVING max((SELECT T1.number_of_travelers_per_guide FROM Trips AS T1 WHERE T.GuideID=T1.GuideID))
;

i will appreciate any help