WITH productNames (Name, Description, DescriptionShort) AS
(
SELECT locName.Name, Description, DescriptionShort
FROM ProductsGroups pg
JOIN ProductsLocation locName ON pg.Name = locName.LocationID
),
productNamesDesc AS
(
SELECT p.Name, locDesc.Name as Description, p.DescriptionShort FROM productNames p
JOIN ProductsLocation locDesc ON locDesc.LocationID = p.Description
)
SELECT p.Name, p.Description, locShortDesc.Name FROM productNamesDesc p
JOIN ProductsLocation locShortDesc ON locShortDesc.LocationID = p.DescriptionShort