Combining CTEs - Unino two CTEs and join with 3rd CTE-CodePudding

I am looking to combine multiple CTEs to get a result

Here is my first CTE

    with CTE_EditLibrary
      as
  (
  select A.[EditLibraryId] as 'Library'
          ,B.[EditLibraryId] as 'SubLibraryId'
      ,B.[EditId]
      from [EnumEditLibraries] as A 
      full join [EnumEditLibraries] as B 
      on A.editid = B.editid
      where A.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 1)
      and  B.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 2)
     order by A.editid
      )
      select * from CTE_EditLibrary

I want to join it to the result of these two CTEs

with Cte_origins
as
(
SELECT A.editid, A.EditOriginId as 'Origins', B.DisplayName as 'Origin Name',  Null as 'SubOrigins', null as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
    inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 1)
  and  editid not in (some values)
  --order by EditId--460, 446
  )
, Cte_suborigins
as
(
  SELECT A.editid, B.ParentEditOriginId as 'Origins', Src.DisplayName as 'Origin Name', A.EditOriginId as 'SubOrigins',  B.DisplayName as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
  inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  inner join (select EditOriginId, DisplayName from [EditOrigins] where level = 1) as Src
  on B.ParentEditOriginId = Src.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 2)
  and  editid not in (some values)--2562, 2542
 --order by editid
  )
  select * from Cte_suborigins A
  union all 
  select * from Cte_origins B

I am looking at a full join to get editid, origins, suborigins, library and sublibrary for all edit ids.

I can put the CTEs into #temptables or views and then join however I want to know if this can be done within CTEs without creating and dropping temp tables and views.

If I union 2 ctes and join with 3rd like this, it will throw must have an equal number of expressions

With Cte_......
(
.
.
)
 select * from Cte_suborigins A
  union all 
  select * from Cte_origins B
  full join CTE_EditLibrary C 
  on A.EditId = B.EditId

CodePudding user response：

The error occurs because your unioned selects doesn't have the same amount of columns. If you join two tables, you get both tables' columns. If you would've replace the * with the specific columns, you'd have noticed that the first select in the union cannot access Library or SubLibraryId, and that's because the join is only between Cte_origins and CTE_EditLibrary. Cte_suborigins gets left behind and has fewer columns than the latter.

Either do the join after you've done the union, or join both with both CTEs

 with CTE_EditLibrary as
(
    select 
            A.[EditLibraryId] as 'Library'
    , B.[EditLibraryId] as 'SubLibraryId'
        , B.[EditId]
  from [EnumEditLibraries] as A 
  full join [EnumEditLibraries] as B 
        on A.editid = B.editid
  where A.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 1)
  and  B.EditLibraryId in (select editlibraryid from [EditLibraries] where level = 2)
  order by A.editid
  )
, Cte_origins as(
    SELECT 
            A.editid
        , A.EditOriginId as 'Origins'
        , B.DisplayName as 'Origin Name'
        , Null as 'SubOrigins'
        , null as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
    inner join [EditOrigins] B
  on A.EditOriginId = B.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 1)
  and  editid not in (some values)
  --order by EditId--460, 446
)
, Cte_suborigins as(
  SELECT 
            A.editid
        , B.ParentEditOriginId as 'Origins'
        , Src.DisplayName as 'Origin Name'
        , A.EditOriginId as 'SubOrigins'
        ,  B.DisplayName as 'SubOrigin Name'
  FROM [EnumEditOrigins] A
  inner join [EditOrigins] B
        on A.EditOriginId = B.EditOriginId
  inner join (select EditOriginId, DisplayName from [EditOrigins] where level = 1) as Src
        on B.ParentEditOriginId = Src.EditOriginId
  where A.EditOriginId in (select EditOriginId from [EditOrigins] where level = 2)
  and  editid not in (some values)--2562, 2542
 --order by editid
)
  SELECT *
  FROM (
    select * from Cte_suborigins A
    union all 
    select * from Cte_origins B
  ) d
  full join CTE_EditLibrary C 
    on d.EditId = c.EditId