Cacher is the code snippet organizer for pro developers

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