CREATE TABLE dbo.tblLocations
(
    LocationID int IDENTITY(0,1) NOT NULL,
    LocationName varchar(250) NOT NULL,
    SectionID smallint NOT NULL,
    LocationType smallint NOT NULL,
    ParentLocation int NULL,
    MapFileID int NULL,
    CONSTRAINT PK_Locations_LocationID PRIMARY KEY CLUSTERED (LocationID ASC),
    CONSTRAINT FK_Locations_SectionID FOREIGN KEY (SectionID) REFERENCES dbo.tblSectionInfo (SectionID),
    CONSTRAINT FK_Locations_LocationType FOREIGN KEY (LocationType) REFERENCES dbo.tblLocationTypes (LocationTypeID),
    CONSTRAINT FK_Locations_ParentLocation FOREIGN KEY (ParentLocation) REFERENCES dbo.tblLocations (LocationID),
    CONSTRAINT FK_Locations_MapFileID FOREIGN KEY (MapFileID) REFERENCES dbo.tblFiles (FileID)
)

-- 1 include the parent name in the query
SELECT
	a.*,
	b.LocationName AS [ParentLocationName]
FROM
	dbo.tblLocations a
	LEFT JOIN dbo.tblLocations b ON
		a.ParentLocation = b.LocationID
		

--  2 List all child locations of a parent location
SELECT
	a.*,
	b.LocationName AS [ChildLocationName]
FROM
	dbo.tblLocations a
	LEFT JOIN dbo.tblLocations b ON
		a.LocationID = b.ParentLocation
WHERE 
	a.LocationName = 'SomeParentLocation';
	
-- 3 Number of child locations each location has
SELECT
	a.LocationName,
	COUNT(*) AS Childcount
FROM
	dbo.tblLocations a
	LEFT JOIN dbo.tblLocations b ON
		a.LocationID = b.ParentLocation
GROUP BY
	a.LocationName;