select * from таблица where таблица.id in (select id from таблица where условие)

select * from Subdivisions where id in ( 
 WITH tree (id , name, idParent) 
 AS ( SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t WHERE t.ID = 1 UNION ALL 
 SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t INNER JOIN tree ON ( t.Id_parent = tree.id )) 
 SELECT tr.id FROM tree tr order by tr.id
 )

 WITH tree (id , name, idParent) 
 AS ( SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t WHERE t.ID = 1 UNION ALL 
 SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t INNER JOIN tree ON ( t.Id_parent = tree.id )) 

 WITH tree2 (id , name, idParent) 
 AS ( SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t WHERE t.ID = 1 UNION ALL 
 SELECT t.ID, t.NAME, t.Id_parent FROM Subdivisions t INNER JOIN tree2 ON ( t.Id_parent = tree2.id )) 

 SELECT tr.id FROM tree tr order by tr.id

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
-- Define the first CTE query.  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)  

)  
,   -- Use a comma to separate multiple CTE definitions.  

-- Define the second CTE query, which returns sales quota data by year for each sales person.  
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  

-- Define the outer query by referencing columns from both CTEs.  
SELECT SalesPersonID