TSQL: How to union multiple complicated selections?

Question

TSQL (I use it in Dynamics 365)

I have two select queries. They both use recursion:

-- first selection
with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id)
select Id, pathstr
from tree  with (nolock) order by pathstr;

-- second selection
with tree2 (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr) as nvarchar(160)), 
    tree2.targetId, tree2.targetName
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId = T.BusinessUnitId)
select top(1) Id, pathstr
from tree2 with (nolock) order by len(pathstr) desc;

Separately, both queries work. But I need to union their results into one. How can I do it?

This is my attemption to do it, but it doesn't work:

-- first selection
with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id)
select Id, pathstr
from tree  with (nolock) order by pathstr
union all
-- second selection
with tree2 (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr) as nvarchar(160)), 
    tree2.targetId, tree2.targetName
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId = T.BusinessUnitId)
select top(1) Id, pathstr
from tree2 with (nolock) order by len(pathstr) desc

I get the error:

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'union'.
Msg 319, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

UPDATED:

Thank you all!

It works:

with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id),

tree2 (Name2, Id2, ParentId2, pathstr2, targetId2, targetName2)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr2) as nvarchar(160)), 
    tree2.targetId2, tree2.targetName2
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId2 = T.BusinessUnitId)

select Id, pathstr
from tree  with (nolock) --order by pathstr
UNION ALL
select top(1) tree2.Id2, tree2.pathstr2
from tree2 with (nolock) --order by len(tree2.pathstr2) desc;

But second selection contains not the same records I expected because I commented order by. How can I fix it?

UPDATED2:

I try to solve the oredr by problem:

with tree (Name, Id, ParentId, pathstr, targetId, targetName)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId as targetId, Name
   from BusinessUnit with (nolock)
   where BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
    tree.targetId, tree.targetName
   from BusinessUnit as T  with (nolock)
     inner join tree on T.ParentBusinessUnitId = tree.Id),

tree2 (Name2, Id2, ParentId2, pathstr2, targetId2, targetName2)
as (select Name, BusinessUnitId, ParentBusinessUnitId, Name, BusinessUnitId, Name
   from BusinessUnit with (nolock) 
   where BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
union all
   select T.Name, T.BusinessUnitId, T.ParentBusinessUnitId, cast((T.Name + '/' + tree2.pathstr2) as nvarchar(160)), 
    tree2.targetId2, tree2.targetName2
   from BusinessUnit as T with (nolock) 
     inner join tree2 on tree2.ParentId2 = T.BusinessUnitId)

select Id, pathstr
from tree  with (nolock)
UNION ALL
-- subquery:
(select top(1) tree2.Id2, tree2.pathstr2
from tree2 with (nolock) order by len(pathstr2) desc) -- Error: Incorrect syntax near the keyword 'order'.
order by pathstr

What's wrong with `UNION( ALL)`? It seems you already know the syntax for it, so why not use it? — Thom A, Jun 14 '19 at 18:28
@Larnu, when I try to use it in my case I get error: `Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'union'. Msg 319, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. ` — Andrey Bushman, Jun 14 '19 at 18:30
You need to declare both your CTEs first. `WITH CTE1 AS( SELECT...), CTE2 AS( SELECT...) SELECT ... FROM CTE1, CTE2;` — Thom A, Jun 14 '19 at 18:32
Slight detour...https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere But if you care about accurate results you should stop splattering nolock all over the place. — Sean Lange, Jun 14 '19 at 18:37

Thom A · Answer 1 · 2019-06-14T18:45:37.420

You're trying to start a second WITH inside an existing statement, that isn't how a CTE works. You only need to declare WITH once:

WITH tree ([Name], Id, ParentId, pathstr, targetId, targetName) as (
    SELECT [Name],
           BusinessUnitId,
           ParentBusinessUnitId,
           [Name], --Name again?
           BusinessUnitId AS targetId,
           [Name] --Name again again?
    FROM BusinessUnit WITH (NOLOCK) --Why NOLOCK?
    WHERE BusinessUnitId = '9A8E9F45-0C5C-E911-811E-00505682357C'
    UNION ALL
    SELECT T.Name,
           T.BusinessUnitId,
           T.ParentBusinessUnitId,
           CAST((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
           tree.targetId,
           tree.targetName
    FROM BusinessUnit AS T  WITH (NOLOCK) --T for Business name, not for Tree?
        INNER JOIN tree ON T.ParentBusinessUnitId = tree.Id),
tree2 (Name, Id, ParentId, pathstr, targetId, targetName) AS(
    SELECT [Name],
           BusinessUnitId,
           ParentBusinessUnitId,
           [Name], --Name again?
           BusinessUnitId AS targetId,
           [Name] --Name again again?
    FROM BusinessUnit WITH (NOLOCK) --Why NOLOCK?
    WHERE BusinessUnitId = 'D60D6FCF-FC0E-E311-BA46-00505692007C'
    UNION ALL
   SELECT T.Name,
           T.BusinessUnitId,
           T.ParentBusinessUnitId,
           CAST((tree.pathstr + '/' + T.Name) as nvarchar(160)) as pathstr, 
           tree.targetId,
           tree.targetName
    FROM BusinessUnit AS T WITH (NOLOCK) --T for Business name, not for Tree?
        INNER JOIN tree ON T.ParentBusinessUnitId = tree.Id)
SELECT Id, pathstr
FROM tree WITH (NOLOCK)
UNION ALL
SELECT Id, pathstr
FROM(SELECT TOP(1) Id, pathstr
     FROM tree2 WITH (NOLOCK)
     ORDER BY LEN(pathstr) DESC);

I haven't fixed the other erros here though, as, for example, Name is declared 3 times in your CTEs)

Edit: Added some comments to the SQL.

Yeah, that needs to go in a Sub Query now I think about it. Ty @TheImpaler — Thom A, Jun 14 '19 at 18:45
@Larnu, Thank you. I added `UPDATED` section in my question. — Andrey Bushman, Jun 14 '19 at 18:48
Don't update our question with the answer @AndreyBushman, upvote and/or mark as the solution the answer that helped you get the answer you needed. That is the best way to show your appreciation and let future readers know the answer helped. — Thom A, Jun 14 '19 at 18:51
@Larnu I added voites and I will set marked answer when my problem will be solved complettely. I added UPDATED2 section in my question - I do it for show my next state of the problem solving (for history). — Andrey Bushman, Jun 14 '19 at 19:04

score 1 · Answer 2 · answered Jun 14 '19 at 18:41

1

The ORDER BY clauses you are using in the main queries can be tricky since a UNION doesn't allow all possibilities you may think of. Your union-ed query should look like:

with
tree (...) as (
  ...
),
tree2 (...) as (
  ...
)
select Id, pathstr
from tree 
union ( -- parenthesis needed because of the inner ORDER BY
  select top(1) Id, pathstr
  from tree2 order by len(pathstr) desc
)
order by pathstr -- you do want to order by pathstr, right?

answered Jun 14 '19 at 18:41

The Impaler

45,731
9
39
76

Thank you. I added `UPDATED` section in my question. – Andrey Bushman Jun 14 '19 at 18:48
@AndreyBushman To your UPDATED question. Read our answers (larnu & mine). You need to keep your `ORDER BY` clauses; you need to move the first one to the end, and the second one should be placed in a subquery. – The Impaler Jun 14 '19 at 18:56
I added UPDATED2 section in my question. – Andrey Bushman Jun 14 '19 at 19:00

TSQL: How to union multiple complicated selections?

2 Answers2