Recursive lookup on Multiple Tables with conditional

I have multiple tables from which I need to extract Parent/Child info for each item in one of the tables. So for example:

Table1:

Table2:

Table3:

So for each item in Table1, it should return the full parental hierarchy along with the description from Table2 IF the Name field in Table2 contains > character AND Only use the description from the first Name field with the > character. So for example:

I'd asked a similar question and thought I would be able to figure out the conditional description but no luck so far.

I would greatly appreciate your help!

[UPDATE]
For clarification:

1. In Table2, the Location Column holds the IDs of Table3 - thus for clarity I've update the Location column to IDFromTable3 in Table2

2. the challenge for me is to introduce a check while doing recursion such that IF there exists in Table2 Name column a '>' character, then the item in Table1's Description shall be updated to the description from Table2's first occurrence (of > character - if for example during recursion on Table2 more than 1 GrandParentID items are encountered that have '>' character in the Name column) else it shall use the description from its own (Table1) row

3. I've fixed the Table1 to reflect the updated column LocationIDFromTable3 -- so in other words we can use the Table3 ID directly from Table1 instead of going to Table2

You need a recursive CTE and aggregation utilising SQLite's feature of bare columns:

WITH cte AS (
  SELECT id, Name, Weight, Height, Description, Name Lineage, ParentIDFromTable2, LocationIDFromTable3, 
             false flag, 0 level 
  FROM Table1
  UNION ALL
  SELECT c.id, c.Name, c.Weight, c.Height, 
         CASE WHEN NOT flag AND t2.Name LIKE '>%' THEN t2.Description ELSE c.Description END, 
         TRIM(t2.Name, '>') || '/' || c.Lineage,
         t2.GrandParentID,
         c.LocationIDFromTable3,
         flag OR t2.Name LIKE '>%',
         c.level + 1
  FROM Table2 t2 INNER JOIN cte c 
  ON c.ParentIDFromTable2 = t2.id
)
SELECT t.id, t.Name, t.Weight, t.Height, t.Description, 
       t3.Name || '/' || t.Lineage Lineage 
FROM (SELECT *, MAX(level) FROM cte GROUP BY id) t 
INNER JOIN Table3 t3
ON t3.id = t.LocationIDFromTable3
GROUP BY t.id;

If the char '>' may be anywhere inside the value of Name in Table2 and not only at the beginning, then replace t2.Name LIKE '>%' with INSTR(t2.Name, '>').<br/>

See the demo.<br/>

Here's one way to do it with recursion:

• "Base step": gather information from tab1
• "Recursive step": join step n-1 cte with tab2 on matching cte.ParentIDFromTable2 = tab2.id. Make >-prefixed names to have their description recognizable, by concatenating the very same character at the beginning.

Once recursion is done, you can:

• gather the first relevant description with FIRST_VALUE, and order records to...
• make aggregation using GROUP_CONCAT on names, to get your lineage
• lastly join with tab3 to gather ancestor infos

each carried out in a subquery.

WITH RECURSIVE cte AS (
	SELECT id AS firstId, name AS firstName, 
           NULL AS lastId, name, ParentIDFromTable2, Weight, Height,
           Description, LocationIDFromTable3 AS Location
    FROM tab1

    UNION ALL
  
    SELECT cte.firstId, cte.firstName, 
           tab2.id, TRIM(tab2.Name, '>'), tab2.GrandParentID, NULL, NULL, 
           CASE WHEN SUBSTR(tab2.Name, 0, 2)='>' 
                THEN '>' || tab2.Description
           END AS Description, NULL
    FROM       cte 
    INNER JOIN tab2
            ON cte.ParentIDFromTable2 = tab2.id
), cte2 AS (
	SELECT firstId, firstName, Weight, Height, name, lastId,
           FIRST_VALUE(Description) OVER(
               PARTITION BY firstId 
               ORDER     BY Description IS NULL,
                            SUBSTR(name, 0, 2)='>' DESC, 
                            lastid DESC
           ) AS Description, 
           Location
    FROM cte 
    ORDER BY firstId, ParentIDFromTable2
), cte3 AS (
    SELECT firstId                 AS id, 
           firstName               AS name,
           MAX(Weight)             AS weight,
           MAX(Height)             AS height,
           MIN(Description)        AS description,
           MAX(Location)           AS location,
           GROUP_CONCAT(name, '/') AS lineage
    FROM cte2 
    GROUP BY firstId, 
             firstName
)
SELECT cte3.id, 
       cte3.name, 
       cte3.weight, 
       cte3.height, 
       TRIM(cte3.description, '>') AS description, 
       tab3.name || '/' || cte3.lineage AS lineage
FROM       cte3
INNER JOIN tab3
        ON tab3.id = cte3.location

Output:

Check the demo here.

EDIT: An attempt at removing some of the columns to be added at the end is the following:

• keep all the necessary, to be updated, columns in the recursion cte,
• add a last join operation to recover the fields you were missing

In the following example, we're going to remove the fields "weight" and "height", to add them in the last step.

WITH RECURSIVE cte AS (
	SELECT id AS firstId, name AS firstName, 
           NULL AS lastId, name, ParentIDFromTable2,
           Description, LocationIDFromTable3 AS Location
    FROM tab1

    UNION ALL
  
    SELECT cte.firstId, cte.firstName, 
           tab2.id, TRIM(tab2.Name, '>'), tab2.GrandParentID, 
           CASE WHEN SUBSTR(tab2.Name, 0, 2)='>' 
                THEN '>' || tab2.Description
           END AS Description, NULL
    FROM       cte 
    INNER JOIN tab2
            ON cte.ParentIDFromTable2 = tab2.id
), cte2 AS (
	SELECT firstId, firstName, name, lastId,
           FIRST_VALUE(Description) OVER(
               PARTITION BY firstId 
               ORDER     BY Description IS NULL,
                            SUBSTR(name, 0, 2)='>' DESC, 
                            lastid DESC
           ) AS Description, 
           Location
    FROM cte 
    ORDER BY firstId, ParentIDFromTable2
), cte3 AS (
    SELECT firstId                 AS id, 
           firstName               AS name,
           MIN(Description)        AS description,
           MAX(Location)           AS location,
           GROUP_CONCAT(name, '/') AS lineage
    FROM cte2 
    GROUP BY firstId, 
             firstName
)
SELECT cte3.id, 
       cte3.name, 
       tab1.weight, 
       tab1.height, 
       TRIM(cte3.description, '>') AS description, 
       tab3.Name || '/' || cte3.lineage AS lineage
FROM       cte3
INNER JOIN tab1 
        ON tab1.id = cte3.id
INNER JOIN tab3
        ON tab1.LocationIDFromTable3 = tab3.id

Check the demo here.