1

let's say I have a column named "parent" that references the ID column from the same table. so it can empty or a number. if it's empty it means that the record has no parent.

Example:

ID  name  parent
1   A
2   B     1
3   C     2
4   D     2

to get the ancestor of C I make two queries:

SELECT parent FROM table WHERE id = 2

SELECT parent FROM table WHERE id = 1

then I get empty parent so I know 1 is the ancestor.

I was wondering if it's possible to do this in a single query :)

Dan Bracuk
  • 20,428
  • 4
  • 25
  • 41
Anna K.
  • 1,727
  • 3
  • 24
  • 35

2 Answers2

1

I don't think you can do it in a single query, but with recursive_triggers (SQLite>=3.6.18) you can do it with a fixed number of statements.

Check this (where tt is your table name):

-- Schema addition:
PRAGMA recursive_triggers=1;
CREATE TEMP TABLE ancid(id UNIQUE, ancestor);
CREATE TEMP TRIGGER ancid_t AFTER INSERT ON ancid WHEN (SELECT parent FROM tt WHERE id=NEW.ancestor) IS NOT NULL BEGIN
    INSERT OR REPLACE INTO ancid SELECT NEW.id, parent FROM tt WHERE id=NEW.ancestor;
END;

-- Getting ancestor from id=3:
INSERT INTO ancid VALUES(3, 3);
SELECT * FROM ancid WHERE id=3;

-- Getting all ancestors:
INSERT OR REPLACE INTO ancid SELECT id, id FROM tt;
SELECT * FROM ancid;
LS_ᴅᴇᴠ
  • 10,569
  • 1
  • 21
  • 46
-1

Yes there is Using Recursive CTE

in essence it will be. This is pseudo code but it will get you 90% there. If you give me some table definitions i can do more for you

;with coolCTE as (
SELECT id,NAME,1 as level
FROM tableX
where parent is null
union all
select id,name,level + 1
from tablex as y 
inner join coolcte as c on y.id = c.parentid
where y.parentid is not null
)
gh9
  • 9,229
  • 8
  • 61
  • 92