TWO RECURSIVE SELECTS UNION IN SQLITE

Question

I want to ask question, how can I UNION these two recursive SELECTS together into the one result set. Thank you for answer

WITH sub_tree (Item_id,Item_name,Item_fyz,Item_fyz_hodnotaID)
                     AS
                     (Select A.id,A.name,A.fyz1_x_id,A.fyz_hodnota_id
                     from fyz_hodnota AS A
                     where A.id = 29
                     UNION ALL
                     SELECT BF.id, BF.name,BF.fyz1_x_id, BF.fyz_hodnota_id
                     from fyz_hodnota AS BF
                    inner join sub_tree st on  st.Item_id = BF.fyz_hodnota_id)
                    SELECT DISTINCT * from sub_tree

WITH sub_tree (Item_id,Item_name,Item_fyz,Item_fyz_hodnotaID)
                     AS
                     (Select A.id,A.name,A.fyz1_x_id,A.fyz_hodnota_id
                     from fyz_hodnota AS A
                     where A.id=27
                     UNION ALL
                     SELECT BF.id, BF.name,BF.fyz1_x_id, BF.fyz_hodnota_id
                     from fyz_hodnota BF
                     inner join sub_tree st on st.Item_fyz_hodnotaID = BF.id)
                     SELECT * from sub_tree

score 0 · Accepted Answer · answered Dec 15 '20 at 10:39

Declare the 2 CTEs under the same WITH clause and apply UNION:

WITH 
sub_tree1(Item_id,Item_name,Item_fyz,Item_fyz_hodnotaID) AS (
  Select A.id,A.name,A.fyz1_x_id,A.fyz_hodnota_id 
  from fyz_hodnota AS A where A.id = 29 
  UNION ALL 
  SELECT BF.id, BF.name,BF.fyz1_x_id, BF.fyz_hodnota_id 
  from fyz_hodnota AS BF inner join sub_tree1 st 
  on st.Item_id = BF.fyz_hodnota_id
),
sub_tree2(Item_id,Item_name,Item_fyz,Item_fyz_hodnotaID) AS (
  Select A.id,A.name,A.fyz1_x_id,A.fyz_hodnota_id 
  from fyz_hodnota AS A 
  where A.id=27 
  UNION ALL 
  SELECT BF.id, BF.name,BF.fyz1_x_id, BF.fyz_hodnota_id 
  from fyz_hodnota BF inner join sub_tree2 st 
  on st.Item_fyz_hodnotaID = BF.id
)
SELECT DISTINCT * from sub_tree1
UNION ALL
SELECT * from sub_tree2

TWO RECURSIVE SELECTS UNION IN SQLITE

1 Answers1