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
WITH recursive_statement (id, folder) AS
SELECT name, folder
WHERE name = 'object1'
SELECT t.name, t.folder
FROM table t
INNER JOIN recursive_statement rs
ON t.name = rs.folder
WITH recursive_statement (...) AS
-- my recursive statement which takes the object name in question from outside
-- Somehow get the object name inside the with clause from here
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.