Recursive query

• jvdb 65877

  Grasshopper

  Points: 17

  More actions

  May 8, 2012 at 8:54 am

  #268620

  Dear,

  I'm struggling with a recursive query.

  The bill of material of our products is stored in a table 'REQUIREMENTS'.

  In this table you have the column 'WORKORDER_BASE_ID' which is the assembly and

  you have the column 'PART_ID' which are the parts that are used for the assembly.

  e.g.

  WORKORDER_BASE_ID PART_ID

  Assembly 1 P1

  Assembly 1 P2

  So Assembly 1 exists of P1 and P2

  Now is it possible that P1 from the example is also a assembly of his own.

  e.g.

  WORKORDER_BASE_ID PART_ID

  Assembly 1 P1

  Assembly 1 P2

  P1 P3

  P1 P4

  The outcome that I would like to have is a recordset of all the parts with their highest assembly

  so for the example : P1 => Assembly 1 but P3 => Assembly 1 (via P1)

  So a typical example of a recursive query but I can't work out the code :

  (sideremark : the reason for WORKORDER_TYPE = 'M' => not only the default BOM is stored in that table but also the produced work orders and their materials are stored there so I have to filter the defaults BOM by filtering on type 'M')

  WITH MaxMaster as

  (SELECT DISTINCT REQUIREMENT.WORKORDER_BASE_ID as PART, REQUIREMENT_1.WORKORDER_BASE_ID as MASTERS FROM REQUIREMENT LEFT JOIN

  REQUIREMENT AS REQUIREMENT_1 ON (REQUIREMENT.WORKORDER_TYPE = REQUIREMENT_1.WORKORDER_TYPE) AND

  (REQUIREMENT.WORKORDER_BASE_ID = REQUIREMENT_1.PART_ID) WHERE (((REQUIREMENT.WORKORDER_TYPE)='M') AND

  ((REQUIREMENT.PART_ID) Is Not Null) AND ((REQUIREMENT_1.WORKORDER_BASE_ID) Is Null)) UNION ALL

  SELECT PART_ID AS Part, WORKORDER_BASE_ID AS Masters FROM REQUIREMENT INNER JOIN MaxMaster ON

  (REQUIREMENT.WORKORDER_TYPE='M' AND REQUIREMENT.PART_ID Is Not Null AND WORKORDER_BASE_ID = MaxMaster.Part))

  Select * from MaxMaster

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  May 8, 2012 at 9:33 am

  #1484605

  Hi and welcome to SSC! Please take a look at the first link in my signature for best practices on posting questions. At the very least we need ddl, sample data and desired output. You will be rewarded with tested and fast code. Without something to work with we can't do much to help you write your sql.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/