0

I have a data table with about the following schema:

ID (Int)             --ID of the entry
Parent_ID (Int)      --ID of the parenting entry, or 0 if there is none
Level (Int)          --Level in the hierarchy of this entry. Starts at 1. 
User (int)           --ID of the user who owns this entry

I've seen this type of hierarchy referred to as BOM (bill of material).

I must admit, I'm not very experienced with how to approach BOM from a SQL perspective. It doesn't seem that difficult to approach it from procedural code in an application but SQL is another beast here.

Anyway, what I would like to achieve is to get all entries of a certain user with the ID X. But since it's possible that entries earlier in the hierarchy belong to another user, just filtering with WHERE user = X may and in all likelihood will break the hierarchy.

In other words, I need all entries WHERE user = X but also all entries higher up in the hierarchy.

I'm currently reading up on CTEs as I have an inkling this might be the right approach but I'd appreciate any hints in the right direction. If you have a working code example you're willing to share, that would be much appreciated, too.

6
  • Have a look at this question: Can I get a tree-structure from a self-referenced (hierarchical) table?
    – McNets
    Dec 12, 2020 at 21:44
  • I will, thanks for the hint.
    – vic
    Dec 12, 2020 at 21:49
  • So just to confirm, if for example your BOM structure is Level 0 (root level) and User = 1234 --> Level 1 and User = 1234 --> Level 2 and User = 5678, you want to be able to filter where User = 5678 and get all of 5678's ancestor level rows right?
    – J.D.
    Dec 12, 2020 at 22:42
  • 1
    @J.D. it looks like there may be typos in your example but generally, yes, that's correct, I wish to get all entries for user 5678 and also all ancestor entries, no matter the user. Level 0 (single root) is not part of the table, by the way, it is just assumed that all entries in the table are subitems of level 0. So, level 0 doesn't have a user but levels 1 and further do.
    – vic
    Dec 12, 2020 at 22:48
  • 1
    Thank you, J.D., I'll try this out right away!
    – vic
    Dec 12, 2020 at 23:06

1 Answer 1

1

As you mentioned you can use a CTE, actually a Recursive CTE, to do what's known in the industry as a BOM Explosion.

If I understand your logic correctly, this is essentially the query of the Recursive CTE you'd want to use.

WITH CTE_Recursive AS -- Recursive CTE to explode the BOM
(
    -- Base Case
    SELECT ID AS ChildId, Parent_ID AS ParentId, [Level], [User]
    FROM BOM
    WHERE [User] = 5678-- Filter on the User to start with here

    UNION ALL -- This allows us to call the CTE recursively

    -- Recursive Case
    SELECT R.Parent_ID AS ChildId, B.Parent_ID AS ParentId, R.[Level] - 1 AS [Level], B.[User] -- Notice how the Parent of the previous call becomes the Child here, as it recursively works its way up the tree (and the new Parent comes from the BOM table, and is the Parent of the previous Parent).
    FROM CTE_Recursive AS R
    INNER JOIN BOM AS B
        ON R.ParentId = B.ID
)

-- Final SELECT for the results
SELECT ChildId, ParentId, [Level]
FROM CTE_Recursive

Let me know how this works out as I don't have any data to test it with at the moment, and you basically want a reverse BOM explosion, since you need to filter on a specific child and get all ancestors. (A regular BOM explosion is what I'm more used to writing recursive CTEs for, where you start with a specific ancestor and get all children.) Also please see the link I provided above for more examples and information on using recursive CTEs.

8
  • @Vic Btw, I updated the User in my example query above because I realized the value I had initially used could've been confusing (and was not related to the example I gave in my other comment). Also any questions, feel free to ask, and I'll do my best to answer them.
    – J.D.
    Dec 12, 2020 at 23:11
  • 1
    J.D. From what I can see, it works perfectly. Thanks so much! Such a clean solution! This has been a tremendous help. I'd really like to send a beer your way as a token of appreciation, do you have a PayPal account or something similar?
    – vic
    Dec 12, 2020 at 23:20
  • @vic haha I'm so glad it worked out so smoothly (I was basically flying blind writing it out without any data ;). I really do appreciate the offer (not even sure if that's allowed on here lol) but I just enjoy spreading the knowledge I have from overcoming previous problems in the past, thank you though anyway! Fortunately, I've worked with BOMs specifically for a manufacturing company in a past life, so this one is something I'm familiar with. :) If you have any further questions, always feel free to reach out.
    – J.D.
    Dec 12, 2020 at 23:29
  • 1
    Blindly? You clearly know your stuff! :-) Well, my offer stands! I just linked my web page, you can drop me a line there any time. Thanks again!
    – vic
    Dec 12, 2020 at 23:38
  • 1
    I just noticed that there are duplicates in the result set. If an entry is parent to 2 entries, then the parent will appear twice in the result set (or as many times as there are child entries). Is this just a matter of adding a DISTINCT keyword in the last select statement?
    – vic
    Dec 13, 2020 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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