We have a BOM (Bill of material) table according below...

Now it has been found that the material included in itself in certain components and subcomponents. See below what I mean. This is not valid and makes some recursive SQL does not work. My question is how through SQL can identify which materials are included in itself as a component. It may be a level 1 or 2 or less that they can be included.

Ex:

Material--------Component-------Quantity--------Materialtype
A---------------B---------------1---------------M
A---------------C---------------2---------------M
A---------------D---------------1---------------M
B---------------E---------------1---------------P
B---------------F---------------2---------------P
C---------------A---------------1---------------P --->Invalid
C---------------G---------------1---------------P
D---------------D---------------1---------------P --->Invalid
D---------------H---------------2---------------P
H---------------A---------------1---------------P --->Invalid
H---------------I---------------1---------------P

asked 06 Sep '16, 14:05

Rolle's gravatar image

Rolle
345182539
accept rate: 0%

edited 06 Sep '16, 14:33

Please show us the details of your table (i.e. the CREATE TABLE statement - is it just one table?) and some insert statements with sample data and the queries you have tried so far...

What SQL Anywhere version do you use ("select @@version")?

(07 Sep '16, 01:01) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17

question asked: 06 Sep '16, 14:05

question was seen: 168 times

last updated: 07 Sep '16, 01:01