Select all recursive value with a single SQLite query

Question

I have another case:

I created the table first:

 CREATE TABLE tree(
     id_tree integer PRIMARY KEY AUTOINCREMENT,
     id_boss TEXT,
     id_child TEXT,
     answ TEXT);

enter some values:

 INSERT INTO tree(id_boss,id_child,answ) VALUES('1','8','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('1',null,'F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('8','P1','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('8','2','F');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','P2','T');
 INSERT INTO tree(id_boss,id_child,answ) VALUES('2','P3','F');

and execute the request:

  WITH RECURSIVE
  ancestors(id, answ) AS (
  VALUES('P3', 'T')
  UNION ALL
  SELECT tree.id_boss, tree.answ
  FROM tree JOIN ancestors ON tree.id_child = ancestors.id
  )
  SELECT id FROM ancestors WHERE answ = 'T';

result:

  P3
  1

that for P3, and I want to create a list with all the recursive value, so it will look like this:

   1 --- // P3
   1 --- // P1
   8 --- // P1
   2 --- // P2
   1 --- // P2

Answer 1

Is it somewhere close to what you are looking for?

WITH seed (id, answ) as ( VALUES('P3', 'T') )
   , ancestors1(id, answ) AS (
        select * from seed
        UNION ALL
        SELECT tree.id_boss, tree.answ
        FROM tree, ancestors1 where tree.id_child = ancestors1.id
     )
   , ancestors2(id, answ) AS (
        select * from seed
        UNION ALL
        SELECT tree.id_boss, tree.answ
        FROM tree, ancestors2 where tree.id_child = ancestors2.id
    )
select id from (
    select * from ancestors1
    union all
    select * from ancestors2
);

I had to rephrase some things to get thenm to compile with db2 (remove RECURSIVE and use implicit connection)