April 1, 2002 at 9:05 am
I have a table set up where every part in an assembly that has sub-parts is set up in a table with a parent_id column and a part_id column. If a part exists in a table I need to recursively look up the part to find all sub parts, sub-sub parts, sub-sub-sub parts, etc. to build a Bill of Materials. I have VB front end but was trying to minimize the round trips to the database. Any suggestions?
April 1, 2002 at 10:33 am
You could use recursion,but it be very dangerous and I do not recommend it. I've easily screwup up a desktop with recursion and I'd hate to whack my db server with a mistake.
I have done this with a table where I loop through and insert values into the table as I find them. Then select the results from this table. Make either a perm or temp table.
Steve Jones
April 9, 2002 at 10:13 am
We've come across this problem some time ago and the solution offered was developing two tables. Let's call it Table1 and Table2.
Table1 contains all objects (with their properties) including two essential fields(identifier and parent identifier). Table2 contains all descendant objects for every object in Table1. Surely we had to write stored procedures to create, update or delete new object but the operation of retrieving of child objects got much easier.
Please, e-mail me if you'tr confronted with troubles.
April 10, 2002 at 12:11 am
here's a simple recursion that lists all the parts which are descendents to a part_id
DECLARE @Parts TABLE
(
Colidint identity,
Part_idint,
Part_novarchar(10),
Parent_idint
)
DECLARE @Colidint
DECLARE @PartIdint
SET @Partid = 1--Parameter passed
SET @Colid = 1
INSERT INTO @Parts
SELECT Part_id,Part_no,@Partid
FROM Parts
WHERE Part_id = @Partid
WHILE 0 = 0
BEGIN
SELECT @Partid = Part_Id
FROM @Parts
WHERE Colid = @Colid
INSERT INTO @Parts
SELECT Part_id,Part_no,@Partid
FROM Parts
WHERE Parent_id = @Partid
IF (SELECT COUNT(*) FROM @Parts) = @Colid
BEGIN
BREAK
END
SET @Colid = @Colid + 1
END
you can extend this by adding a index to denote which level does the part fall under
Hope this helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply