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,699
  • 4
  • 26
  • 43
Anna K.
  • 1,887
  • 6
  • 26
  • 38

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,823
  • 1
  • 23
  • 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
  • 10,169
  • 10
  • 63
  • 96