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.KirkSQL 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_Assembly1 A T3 B 4 C 5 D 6 E 7 F 9 G 10 H 12 I 13 J 14 K T15 L T16 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 Parent300 3 1400 4 3500 5 4 600 6 3 700 7 3900 9 7100 10 7120 12 9130 13 3160 16 14170 17 15180 18 14190 19 16200 7 17
"
Query on reflexive / recursive table 
Posted - 2004-09-16 : 08:20:33
