gistfile1.txt

-- Designation Table Structure
Id 
Name
Route 

-- Many To Many Relationship
DesignationId
CommunityId

-- Community Table Structure
Id

	SELECT
		
		designation.Name				as [Name],
		designation.Route				as [Route],
		COUNT(link.communityId)				as [Count]
		
                -- *************************
                -- If a designation's route is null, we don't care about
                -- selecting it's name, we just want the count added to
                -- the default designation, identified by Id: 1
                -- *************************

	FROM tbl_Designations designation
	
	INNER JOIN tbl_DesignationLink link
		ON link.designationId = designation.Id
		
	INNER JOIN tbl_Communities community
		ON community.Id = link.communityId

	WHERE
	
        -- where clause ommitted for brevity (a bunch of community fields to make sure we're only counting "active" communities)

	GROUP BY designation.Id, designation.Name, designation.Route
	ORDER BY designation.Id