I have three relevant tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key Part part number PartGroupID Foreign key
1 T1000 5 2 wizbang gold 17 3 flux capacitor 2
PartGroup is an Acyclic Directed Graph(tree) modeled with a self-linked parent key
PartGroupID artificial primary key Description name of group ParentID foreign key linked to PartGroupID MarkupGroupID foreign key linked to MarkupGroup
Sample data would look like this-
1 system null null 2 component null 1 3 software null 2 4 abc 1 3 5 xyz 1 4 6 123 4 null 7 456 4 null 8 789 5 null 9 a1 6 null 10 b2 6 null 11 c3 7 null 12 d4 7 null 13 e5 8 null 14 f6 8 null 15 alpha 3 null 16 beta 3 null 17 gamma 3 null
MarkupGroup is to apply a markup factor to several PartGroups as a set.
MarkupGroupID primary key MarkupFactor numeric attribute field
1 15 2 20 3 25 4 22
I need to write a query that returns the appropriate markup amount for each part in the parts table. There is never a case when more then one markup can apply to a part. I need to recurse up the graph to find the markup, and I won't know at query time how many levels I need to recurse to find a non-null markup.
There is never a case when a part will encounter more than one possible join to a margin on the way up the tree, so there's no accumulation that needs to be done.
The t1000 is of type xyz, which has a foreign key to a MarginGroup, so we can join and get a value. It also has a parent, but that's irrelevant since we have the value we need.
Wizbang Gold is gamma-group software, which has no foreign key, but the parent node 'software' does and we should return that.
A flux capacitor is a component, and it has a direct foreign key to a MarginGroup. Return that value.
So the results would be:
1 T1000 22 2 wizbang gold 20 3 flux capacitor 15
I'm pretty sure I'm going to need a recursive CTE and some flavor of APPLY to get this, but my brain is not working very well at the moment. If not for the recursive nature of the middle table this would be pretty straightforward. Platform is MS-SQL.