Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Query on reflexive / recursive table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 09/16/2004 :  08:20:33  Show Profile  Visit AskSQLTeam's Homepage
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.

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

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 09/16/2004 :  11:39:29  Show Profile  Visit Seventhnight's Homepage
try this out for some ideas:


also for more related queries:

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000