SELECT
  haygroveReportingStructures.Id,
  haygroveReportingStructures.ParentID,
  [haygroveReportingStructures].MemberId,
  haygroveReportingStructures.Name,
  haygroveReportingStructures.IsDeleted,
  (SELECT umbracoContentVersion.text FROM umbracoContentVersion WHERE umbracoContentVersion.nodeId = [haygroveReportingStructures].countryId AND umbracoContentVersion.[current] = 1) AS countryName
FROM [haygroveReportingStructures] 
INNER JOIN [umbracoContentVersion] ON [umbracoContentVersion].nodeId = [haygroveReportingStructures].MemberId 
WHERE haygroveReportingStructures.IsDeleted = 0
ORDER BY haygroveReportingStructures.Id ASC