title: joining multiple common table expressions
body: <p>I have two Query, Query1:</p>
  
  <pre><code>with cte as (
  select
          dbo.Cable.*,
          row_number() over(partition by dbo.Cable.TagNo order by dbo.Cable.CableRevision desc) as rn
      from dbo.Cable 
      where (dbo.Cable.CableRevision = @CoreRevision )  
      )
  select *
  from cte
  where rn = 1
  </code></pre>
  
  <p>and also  Query2</p>
  
  <pre><code>with cte as (
  select
          dbo.Cable.TagNo,dbo.Core.*,
          row_number() over(partition by dbo.Core.CoreNo order by dbo.Core.CoreRevision desc) as rn
      from dbo.Core INNER JOIN 
       dbo.Cable ON dbo.Cable.Id = dbo.Core.CableId
      where  (dbo.Core.CoreRevision &lt;= @CoreRevision  )
      )
  select *
  from cte
  where rn = 1
  </code></pre>
  
  <p>these two query are related by <code>Query1.TagNo</code> and <code>Query2.TagNo</code> 
  how can i use join these two querys, is it possible to do that with <code>With</code> Command?</p>
  
  <p>Thank you</p>