Create a documents table:
CREATE TABLE documents (objectid char(16), objectname char(80))
INSERT INTO documents (objectid, objectname)
VALUES ('456', 'Test document'), ('123', 'Folder'), ('121', 'Upper folder'), ('120', 'Parent')
Create a relationships table:
CREATE table relationships (objectid char(16), parent char(16))
INSERT INTO relationships (objectid, parent)
VALUES ('121','120'), ('123', '121'), ('456','123')
I am trying to use a SQL query to return the top relationship of each document like so:
| objectid | topcontainer |
|---|---|
| 120 | 120 |
| 121 | 120 |
| 123 | 120 |
| 456 | 120 |
I tried to use the following query, but it is not working. Not sure why but it terminates.
WITH c AS
(
SELECT d.objectid, r.parent, r.parent AS topcontainer
FROM documents d
JOIN relationships r ON d.objectid = r.objectid
UNION ALL
SELECT
t.objectid, t.parent, c.topcontainer
FROM
relationships AS t
JOIN c ON t.parent = c.parent
)
SELECT objectid, topcontainer
FROM c
ORDER BY c.objectid
1
The following returns the results you wish to achieve. I refactored it a bit to make it clearer (only joining in the recursive section makes more sense to me), but the key issue was in fact that this JOIN c ON t.parent = c.parent should have been this JOIN c ON t.objectid = c.parent i.e. the ParentId of the query should join to the ObjectId of the joined relationship, giving a new ParentId and so on.
And then to get the results you desire, we need to filter out all but the topmost recursion result per document. This requires a second CTE to compute the max level because we can’t use the result in the WHERE clause in the same query its calculated.
WITH c1 AS
(
SELECT d.ObjectId, d.ObjectId ParentId, 0 Level
FROM documents d
UNION ALL
SELECT c1.ObjectId, t.Parent ParentId, Level + 1
FROM c1
JOIN relationships AS t
ON t.ObjectId = c1.ParentId
), c2 AS (
SELECT ObjectId, ParentId, Level
, MAX(Level) OVER (PARTITION BY objectid) MaxLevel
FROM c1
)
SELECT ObjectId, ParentId
FROM c2
WHERE Level = MaxLevel
ORDER BY ObjectID;
| ObjectId | ParentId |
|---|---|
| 120 | 120 |
| 121 | 120 |
| 123 | 120 |
| 456 | 120 |
Fiddle