JEFinster JEFinster - 2 months ago 6
SQL Question

How to implement a recursive with clause in larger sql statement

I'm creating a large sql query involving some recursive with clauses and I'm in need of some ideas on how to "connect" them with the rest of the query.

First, what I want to accomplish. I have a table like this:

ID NAME FOLDER
1 object1 folderA
2 object2 folderB
3 object3 folderC
4 folderA folderD
5 folderB folderD
6 folderD folderE
7 folderC NULL
...


So, objects are listed here and the folders these objects are placed in. What I'd like to know now, as part of a bigger query, is an object placed in a specific folder or (thats where the recursive part come in) in a subfolder? Like: Is object1 located in (or in a subfolder of) folderE? If so, I'd like all the subfolders listed.

I managed to get an answer through a recursive with clause

WITH recursive_statement (id, folder) AS
(
SELECT name, folder
FROM table
WHERE name = 'object1'
UNION ALL
SELECT t.name, t.folder
FROM table t
INNER JOIN recursive_statement rs
ON t.name = rs.folder
)
SELECT *
FROM recursive_statement


This will give me a very good result. I.e. for object1 I get

NAME FOLDER
object1 folderA
folderA folderD
folderD folderE
folderE NULL


But I have trouble inserting this result in my bigger query. I can not figure out how to get the name of the object into the with clause. The object name in question is defined inside the with clause and can not be set from oustide. If I call the recursive statement from outside with "WHERE name = 'object1'" then I get 1 line (of course) saying 'object1 -> folderA'. That doesn't help me.

What I would need is to be able to get the object name inside the statement. Like so:

WITH recursive_statement (...) AS
(
-- my recursive statement which takes the object name in question from outside
)
SELECT *
FROM recursive_statement
-- Somehow get the object name inside the with clause from here

Answer

You can save the original name in the cte and query that:

WITH recursive_statement (id, folder) AS ( 
SELECT name as OriginalName, name, folder 
FROM table 

UNION ALL 

SELECT rs.OriginalName, t.name, t.folder 
FROM table t 
INNER JOIN recursive_statement rs 
ON t.name = rs.folder 
 ) 

SELECT * 
FROM recursive_statement
Where OriginalName = 'object1'

The recursion only happens when the cte is being queried, so performance shouldn't be affected.

Comments