Hello and thank you for taking time to assist me. I am trying to calculate a new column for my table using MySQL and could really use some help.
I have a genealogy table with a column for a parent id and its associated child id. A component_id can simultaneously be the child_id for a different component_id in the same column.
Example 1:
The full genealogy tree will look something like this:
My goal is to create a 3rd column that shows the ID on top of the genealogy tree for every row in the component_id column. Some components might only be a step away, but some could be the 6th child down from the top parent.
Example 2:
I made an attempt to at least show the top parent for 1 specific component_id, but only managed to show its parent. I could not figure out how to make a corresponding column for the whole table either. Here is my code:
WITH DATA AS
(SELECT DISTINCT component_id AS parent, child_id AS child
FROM genealogy
WHERE component_id IN ('S1')
)
SELECT IF(parent = "D", parent,
(select distinct component_id
from genealogy
where datetime_stamp >= Date_Format(Date(Now()), '%Y%m%d') -1
AND child_id = tbl.parent
)) AS 'Parent Parent', parent, child
FROM (SELECT parent, child FROM DATA) as tbl
Current Code Output:
Disclaimer: I have oversimplified to make things easier, so please assume my IDs are complex and will be harder to differentiate than just D1, D2, D3 ...
