Kirk writes "I thought this would be a common parts explosion issue that there would be lots of sample solutions for. But I have come up dry. I hope you guys can help.
I need to create SELECTS that:
1. Correctly order the entire BOM.
2. Single level explosion. Passed the FK for an assembly the BOM for that assembly is returned.
3. Summarized explosion. Passed the FK for an assembly, what is the total quantity of each part required to build the assembly?
I think my tables are properly normalized. And all the information is there I just can't figure out how to write the SELECTs. If the data schema needs to be changed that's ok. I just need a solution.
Thank you for your help.
Kirk
SQL Server 2000 (all service packs)
OS Windows 2000 Pro (all service packs)
I have a regular table that is a parts list.
PK Name is_top_Assembly
1 A T
3 B
4 C
5 D
6 E
7 F
9 G
10 H
12 I
13 J
14 K T
15 L T
16 M
17 N
18 O
19 P
20 Q
A reflexive / recursive table that is a BOM.
For example, Name "B" foreign key 3 is a component of Name "A" foreign key 1.
PK Child_of Parent
300 3 1
400 4 3
500 5 4
600 6 3
700 7 3
900 9 7
100 10 7
120 12 9
130 13 3
160 16 14
170 17 15
180 18 14
190 19 16
200 7 17
"
Query on reflexive / recursive table
Forum Locked
Posted - 09/16/2004 : 08:20:33
