I have a table say 'Product' which is hierarchical in nature. Critical attributes of this table are: Product_id ; Immediate_Parent_Product_id ; Ultimate_Parent_Product_id and using these I am able to traverse the hierarchy. Let's call each hierarchical element as a 'Node'. I have another table say 'Risk Rating' and this table should contain the risk rating (H-High, M-Medium, L-Low) of at least 1 Node from the Product table for a given hierarchy. I need to find any outliers, meaning, is there any hierarchy in the Product table where at least 1 Node does not have a Risk Rating defined in the Risk Rating table.
Appreciate any input how I can achieve this.