Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have three relevent 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 a 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 irrelevent 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.

share|improve this question
    
Edited for clarification. Yes, use the first markup found. –  Jeff Sacksteder Aug 18 '12 at 13:18
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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