0

I have a header table:

BOM_ID, BOM_Name
BOM001, PartA
BOM002, PartB
BOM003, PartC
BOM004, PartD

And a Line table

BOM_ID, Item_Name
BOM001, PartB
BOM001, PartC
BOM002, PartD
BOM003, PartE
BOM003, PartF
BOM004, PartG

I'm looking to get the result:

ParentBOMID, ParentBOMName, ChildBOMID, ItemNumber, Level
BOM001, PartA, BOM001, PartB, 1
BOM001, PartA, BOM001, PartC, 1
BOM001, PartA, BOM002, PartD, 2
BOM001, PartA, BOM003, PartE, 2
BOM001, PartA, BOM003, PartF, 2
BOM001, PartA, BOM004, PartG, 3
BOM001, PartA, NULL, NULL, 3

I'm trying to write a recursive query to flatten a ragged hierarchy where for each item header, you have several lines. Each of these lines has a name which matches back to the header of the next join, whereby you join the header to its lines on ID.

Basically, it's like this:

SELECT  h.parent_id, h.parent_name, l.child_id, l.child_name, 0 AS [level]
FROM header as h
LEFT OUTER JOIN line as l ON h.parent_id = l.child_id

UNION

SELECT h1.parent_id, h1.parent_name,l1.child_id, l1.child_name, 1 AS [level]
FROM header as h1
LEFT OUTER JOIN header as h1 on l.child_name = h1.parent_name
LEFT OUTER JOIN line as l1 on h1.parent_id = l1.child_id

..... and so forth.

I tried using a recursive CTE, but got hung up on the fact that recursive CTEs with outer joins aren't allowed... tried to work my way around it using OUTER APPLY instead of a LEFT OUTER JOIN, but then got caught up in the fact that there are two steps to the join. To solve the problem, I just manually forced it, writing a long list of joins (I showed you the beginning of the chain above). Which worked, because I only needed to go down 6 levels, but it's obviously not the right way.

Dale K
  • 25,246
  • 15
  • 42
  • 71
user22334457
  • 11
  • 1
  • 4
    Without sample data and expected output, how do you expect us to understand what you want??? All we have is a non-working query. – Eric Aug 03 '23 at 17:37
  • 1
    Shouldn't be a problem. Please add a few rows of sample data and the expected result. – The Impaler Aug 03 '23 at 17:48
  • 1
    Without any sample data and expected results this might give you a head start: https://stackoverflow.com/q/18106947/125981 Otherwise it is a duplicate of that – Mark Schultheiss Aug 03 '23 at 18:28
  • 1
    Sorry about that! I've added some data and the desired result - I've never posted a question here before, so I'm not sure about phrasing / formatting. – user22334457 Aug 03 '23 at 19:52
  • 1
    What is the link between your header and line tables? – Eric Aug 03 '23 at 19:56
  • Sorry (wow, I'm not doing too well at this question thing)... there are two links. First, the line item number is linked to the header BOM Name, and then the header BOM_ID links to the BOM ID number of the next layer of lines. – user22334457 Aug 03 '23 at 20:25

0 Answers0