WITH RegionConnections AS (
  SELECT
    ss.regionId AS fromRegionId,
    ss_to.regionId AS toRegionId
  FROM StarGates sg
  JOIN SolarSystems ss ON sg.fromSystemId = ss.solarSystemId
  JOIN SolarSystems ss_to ON sg.toSystemId = ss_to.solarSystemId
  WHERE ss.regionId != ss_to.regionId
),
ConnectedRegions AS (
  SELECT
    fromRegionId,
    GROUP_CONCAT(DISTINCT r2.regionName) AS connectedRegions
  FROM RegionConnections rc
  JOIN Regions r2 ON rc.toRegionId = r2.regionId
  GROUP BY fromRegionId
)
SELECT
  re.regionName AS regionName,
  rml.x,
  rml.y,
  AVG(ss.security) AS security,
  COALESCE(cr.connectedRegions, '') AS connectsTo
FROM RegionMapLayout rml
JOIN Regions re ON rml.regionId = re.regionId
JOIN SolarSystems ss ON ss.regionId = re.regionId
LEFT JOIN ConnectedRegions cr ON re.regionId = cr.fromRegionId
GROUP BY re.regionId;