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.

  • Edited for clarification. Yes, use the first markup found. – Jeff Sacksteder Aug 18 '12 at 13:18

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

with C as
  select P.PartGroupID,
  from PartGroup as P
  where P.ParentID is null
  union all 
  select P.PartGroupID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P 
    inner join C 
      on P.ParentID = C.PartGroupID
select P.PartID,
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,
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
  • In translating this back to my actual environment, I'm having an issue I don't understand yet. The cte returns one row for each possible ancestor relationship, i think. So a part that has two parents would have two rows. As long as the data only contains one markup in the path I can select non-null rows and outer join it to parts directly. Still investigating... – Jeff Sacksteder Aug 18 '12 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.