BOM structure using SQL

Hi there,
Im trying to create a multi-level BOM using MS SQL.
Here is my Table structure:
Table 1:
Part-Number
Table 2:
Part-Number
Part-Owner

So if I link Part-number from T1 to Part-owner I get all parts for that level.
If I link back from T2 using Part-number to T1 I can link again to T2 and check if there are any lower level part structures.
This imbedded link has no limits but usually does not go more then 6-7 levels.
Can anyone help with setting up code for this BOM?

ThanksYour table structure looks valid for logically modeling a hierarchal system. The join you described sounds valid as well.

You might want to add a Where clause and use that in a criteria driven stored procedure that will generate a BOM explosion out of a select, given an assembly SetID; (to return a specific sub part listing result set for a setID representing a desired 'assembly'). Im not sure I understand what you are asking for help with, if you'd like, post an email to me with more specifics, and perhaps I can send you some examples that would help.

Maps
INNER JOIN
Sets
ON Maps.SetID = Sets.SetID
INNER JOIN
Sets Sets_1 ON Maps.SubID = Sets_1.SetID
WHERE
Sets.SetID = [desired SetID]

NOTE:
Sets = "Table 1"
TABLE [Sets] (
[SetID],
[SetName])

Maps = "Table 2"
TABLE [Maps] (
[SetID],
[SubID])|||Hi,
I think you went over my head here
My SQL exposure is somewhat limited. I have not used stored procedures and it sounds from your explanation that I should be using them.
The logic I was going to use was to link Table1 to Table2, then Table2 to Table1 using the other part, however, at some point that becomes imposable.
If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

Thanks much|||RE: If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

Q1[How one may use a stored proc that accepts a parameter?]
A1 OK, I'll try to give you a short example here. If you'd like, post an email to me, and I can send you more complete demo examples.

Example using proc hr_Set_SubSets:
Assume you wish to see a BOM Explosion for a part / assembly number; say for part / assembly number 387. A stored proc that accepts an Int parameter and uses it as criteria on a properly constructed Select statement (built on the Maps and Sets tables) can do just that (assuming the Maps and Sets tables are correctly populated with data). Executing the example proc hr_Set_SubSets proc with 387 assigned to its parameter, would then return all 'components' for the part / assembly with a SetID = 387.

-- Example use of a stored proc hr_Set_SubSets:
Declare
@.vi int
select @.vi = 387
Exec dbo.hr_Set_SubSets @.pSetID = @.vi

-- Creating the stored proc hr_Set_SubSets in the example:
CREATE Proc dbo.hr_Set_SubSets
(@.pSetID int)
AS
SELECT
dbo.Sets.SetName AS SetName,
dbo.Sets.SetID As SetID,
Sets_1.SetName AS SubSetName,
dbo.Maps.SubID As SubSetID
FROM
dbo.Maps
INNER JOIN
dbo.Sets
ON
dbo.Maps.SetID = dbo.Sets.SetID
INNER JOIN
dbo.Sets Sets_1
ON
dbo.Maps.SubID = Sets_1.SetID
WHERE
dbo.Sets.SetID = @.pSetID

Maps and Sets schemas:
TABLE [Sets] (
[SetID],
[SetName])

TABLE [Maps] (
[MapID],
[SetID],
[SubID])sql