Parent-Child Records in SQL Without using CTE Recursive

Transact-SQL
1

Hello everyone,

I am trying to find Root parent id of all children from a table in Azure Synapse using Spark/Python/SQL. Unfortunately, RECURSIVE CTE is not supported. Can anyone help me how to use SQL on a parent-child tree structure without CTE. Here is my sample data:

declare @project table (Projid int
, parent_t_id int
, taskid varchar(2)
, Name varchar
)

insert into @project

select 1, 0, 'T1', 'A' union
select 2, 1, 'T1', 'B' union
select 3, 1, 'T1', 'C' union
select 4, 2, 'T1', 'D' union
select 5, 2, 'T1', 'E' union
select 5, 4, 'T1', 'G' union

select 6, 0, 'T2', 'F' union
select 7, 6, 'T2', 'B' union
select 8, 6, 'T2', 'C' union
select 9, 7, 'T2', 'D' union

select 10, 0, 'T3', 'B' union
select 11, 10, 'T3', 'A' union
select 12, 10, 'T3', 'C' union

select 13, 0, 'T4', 'H' union
select 14, 13, 'T4', 'I' union
select 15, 14, 'T4', 'B' union
select 16, 15, 'T4', 'J'

--select * from @project

Any help will be appreciated.

Thanks

2

With SQL you can join tables multiple times using an alias:

SELECT Project.*, ISNULL(ParentProject.[Name],Project.[Name]) AS ParentProjectName
FROM @project Project
LEFT OUTER JOIN @project ParentProject
ON Project.parent_t_id=ParentProject.Projid;

If you have a complex structure with multiple levels you should take a look if

Hierarchical Data (SQL Server) - SQL Server | Microsoft Learn

is a better approach.

3

Thank you for the quick response RogierPronk.

It is a complex structure and I don't know the dept of the tree. Usually, CTE would have been the solution but Azure synapse don't support it.
Is there a way I can use Union query to achieve it. OR is there python that can do it too?

Thanks

Ralph

4

Python is always an option.

Did you consider a WHILE construction and a #temp table?

5

Thanks RogierPronk

I haven't tried and don't know how to do that. Any idea will be appreciated.

Cheers

6

You could also read up on Nested Sets. The idea is shown in chapter 28 of:

Practical t-sql:

etc

7

I've found this article what might be helpfull:

SQL Hierarchical Queries without Recursion and Cursors (The life before CTE in SQL Server) | by Sanish Abraham | Medium

8

Thank you so much.