Sql-server – Build a three table join with a recusive table in the middle

cross-applyctejoin;sql server

I have three relevant tables: Parts, PartGroup, and MarkupGroup.

Parts is simple.

PartID          artificial primary key
Part            part number
PartGroupID     Foreign key

sample data:

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

sample data-

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.

Best Answer

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you.

with C as
(
  select P.PartGroupID,
         P.ParentID,
         P.MarkupGroupID
  from PartGroup as P
  where P.ParentID is null
  union all 
  select P.PartGroupID,
         P.ParentID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P 
    inner join C 
      on P.ParentID = C.PartGroupID
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  inner join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL Fiddle

The recursive CTE will create a derived table that looks like this.

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

If you need parts with partgroup not connected to a markup you can use a outer join against MarkupGroup in your main query.

with C as
(
  select ...
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  left outer join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID
Related Question