title: Using sql with in where clause
body: <p>How can i use with in a query like this? Is there a way to use the tableIds in TableList? Can i treat them like a subquery?</p>
  
  <pre><code>select * from TestTable where tableId in 
  
  (WITH TableList (tableId)
  AS
  (
  SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
  UNION ALL
  SELECT t.tableId From OldTable as t
  INNER JOIN TableList as p
  ON t.ParenTableId = p.TableId
  WHERE t.IsDeleted = 0
  )
  SELECT tableId FROM TableList
  )
  </code></pre>
  
  <p>EDIT: To show why i cant use with in first statement of sql.</p>
  
  <p><br></p>
  
  <pre><code>select TableId, IsDeleted,
  ( 
    select count(OtherId) OtherTable
    where otherTableId in 
    (WITH TableList (tableId)
    AS
    (
    SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
    UNION ALL
    SELECT t.tableId From OldTable as t
    INNER JOIN TableList as p
    ON t.ParenTableId = p.TableId
    WHERE t.IsDeleted = 0
    )
    SELECT tableId FROM TableList
    )
    ) as Ex1
  from TestTable where tableId in (1,2,3)
  </code></pre>
  
  <p>I think this show why im trying to use with clause in the middle of a sql query. </p>