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. 

 

 

Comments

  • This almost sounds like a join. 

     

    Simple syntax for something like that would be:

    SELECT 
    FROM Dataset AS p
    LEFT JOIN
    (SELECT *
    FROM Dataset
    WHERE type = task) AS t
    ON t.task_parent = p.project
    LEFT JOIN
    (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.

     

    Sincerely,

    Valiant

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!