Help with SQL problem

[pwomack]

I have a table with part and sub-part numbers such as:

Part No Sub-Part No

p1 sp1a
p1 sp1b
p1 sp1c
sp1a s-sp1a1
sp1a s-sp1a2
sp1c s-sp1c
s-sp1a1 s-s-sp1a1a
s-sp1a1 s-s-sp1a1b
s-s-sp1a1b s-s-s-sp1a1a
s-s-s-sp1a1a

There is a main part number (sp1) that has 1 or more sub-part numbers. Each sub-part number may have 1 or more sub-part numbers of it's own. So there is a grandparent-parent-child etc relationship that may be many levels deep. I do no know how many levels there are for any of the parts or subparts.

This process will be passed the main part number (p1). I need to write a stored procedure that will return all of the related part numbers such as the "part no" column in the sample data above.

Does anyone have any hints on how to solve this problem?

Thanks.

 

[starsky51]

I recently wrote a function which does exactly this. I have modified it to fit your requirements:

CREATE FUNCTION dbo.udfGetPartHeirarchy(@PartNo varchar(20))
RETURNS varchar(200)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @ParentNo varchar(20);
    DECLARE @Heirarchy varchar(200) = '';
    
    SELECT @ParentNo = PartNo FROM dbo.Parts WHERE SubPartNo = @PartNo;
    
    WHILE (@ParentNo <> 0)
    BEGIN
        SET @Heirarchy = (@Heirarchy + '(' + @ParentNo + ')');
        SELECT @ParentNo = PartNo FROM dbo.Parts WHERE SubPartNo = @ParentNo;
    END
    
    RETURN @Heirarchy;
END

This assumes that the values in the SubPartNo column are unique.
Using the sample data, the query
SELECT udfGetPartHeirarchy('s-s-s-sp1a1a');
would return the string
(s-s-sp1a1b)(s-sp1a1)(sp1a)(p1)

 

[pwomack]

Thanks for your help on this. However, I'm still struggling. I need to start at the main part level (p1) and work my way down to the lower levels. I need to know all of the sub-parts for a part number.

Subpart 's-s-s-sp1a1a' may be included (occurs many times in the sub-part column) in many main parts, so I can't start there. I need to start at the highest level, which in my case is 'p1'.

Anyone?

 

[fredericofonseca]

Can you please post a sample desired output along with the corresponding input data.

And also please tell us what version of SQL Server you using, as it may be possible to use some functionality available on the latest releases.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[pwomack]

I'm using SQL Server 2000. The output should look similar to the "Part No" column of the test data. I did some Googling and stumbled upon recursive stored procedure calls. This may be what I need.

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

 

[starsky51]

You can use the function to do that:

SELECT * FROM Parts WHERE dbo.udfGetPartsHeirarchy(PartID) LIKE '%(p1)%';