Beast Mode - Parent of Parent type join

I have a data set of project items at multiple levels of a project hierarchy: project > task > sub task. I'm trying to build a sort of rollup card that shows the items based on parent link relationships.   i.e.:

  1. Project Parent = Self
  2. Task Parent = Project
  3. Sub Task Parent = Task

I'd like to be able to have a column that shows the top level project ID for a sub task but I don't actually have the project associated to a sub task in my data set.  I only have the sub tasks parent task.  Is it possible to use beast mode or another solution to get something like:


CASE WHEN `type`='subtask' THEN `subtask_parent.task_parent` 

Where `subtask_parent.task_parent` is the subtask's parent's parent (the project)?


Just looking for options. 




  • This almost sounds like a join. 


    Simple syntax for something like that would be:

    FROM Dataset AS p
    (SELECT *
    FROM Dataset
    WHERE type = task) AS t
    ON t.task_parent = p.project
    (SELECT *
    FROM Dataset
    WHERE type = subtask) AS s
    ON s.subtask_parent = t.task_parent
    WHERE p.`type` = 'parent'

    If something like that is possible with your data, that should get you to what you're looking for.