WITH employeeMaster
AS ( SELECT lp.id ,
lp.ParentId ,
ltp.NAME
FROM Locations lp inner join LocationTranslations ltp on ltp.Language='ar-SY' and lp.Id=ltp.CoreId
WHERE ltp.NAME like '%دم%'
UNION ALL
SELECT lc.id ,
lc.parentid ,
ltc.NAME
FROM employeeMaster cte
INNER JOIN Locations lc ON lc.id = cte.ParentId inner join LocationTranslations ltc on ltc.Language='ar-SY' and lc.Id=ltc.CoreId
)
SELECT *
FROM employeeMaster m