SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

AskSQLTeam
Ask SQLTeam Question

USA
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.
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
"

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

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

http:\\www.seventhnight.com\treestructs.asp

also for more related queries:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39119


Corey
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.05 seconds. Powered By: Snitz Forums 2000