Premium Retail Services

SELECT
  SA.[MdmStoreId]
  , H.[Name] AS Holding
  , D.[Name] AS Division
  , B.[Name] AS Banner
  , SB.[Name] AS SubBanner
FROM
  [StoreAssociation] SA
  JOIN [Store] S ON SA.[MdmStoreId] = S.[MdmId] AND S.[Active] = 1 AND S.CountryId = 'E4B0E7E4-6C79-4AB4-BE9F-53F5AD6A8617'
  JOIN [SubBanner] SB ON SA.[MdmHierarchyGroupId] = SB.[MdmHierarchyGroupId] AND SA.[Active] = 1
  JOIN [Banner] B ON SB.[BannerId] = B.[Id] AND B.[Active] = 1
  JOIN [Division] D ON B.[DivisionId] = D.[Id] AND D.[Active] = 1
  JOIN [Holding] H ON D.[HoldingId] = H.[Id] AND H.[Active] = 1
WHERE
  SA.[Active] = 1
ORDER BY SA.[MdmStoreId]