Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

joining multiple common table expressions

Tags:

I have two Query, Query1:

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

and also Query2

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 <= @CoreRevision  )
    )
select *
from cte
where rn = 1

these two query are related by Query1.TagNo and Query2.TagNo how can i use join these two querys, is it possible to do that with With Command?

Thank you

like image 722
nnmmss Avatar asked Sep 22 '13 07:09

nnmmss


People also ask

Can we join two CTE?

A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you've learned.

Can you reference a CTE in another CTE?

You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE.


1 Answers

Try this query, perhaps this is what you are looking for.

;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
  ), cte2 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 <= @CoreRevision
  )
  SELECT *
  FROM cte c FULL JOIN cte2 c2 ON c.TagNo = c2.TagNo
  WHERE c.rn = 1 OR c2.rn = 1
like image 199
Aleksandr Fedorenko Avatar answered Dec 01 '22 19:12

Aleksandr Fedorenko