SQL newbie, need help cleaning up my SQL (works, but ugly)

Okay, this works, but there has GOT to be a cleaner way of doing this.

Suggestions please.

[HTML]DECLARE @TMP TABLE
(
HOLD1 NUMERIC,
HOLD2 NUMERIC,
HOLD3 NUMERIC,
HOLD4 NUMERIC,
HOLD5 NUMERIC
)
INSERT INTO @TMP (HOLD1, HOLD2)
select PARENT_MEMBER_ID,null
from mydb.RELATIONSHIP
where child_member_id =2824

UPDATE @TMP
SET HOLD2 = PARENT_MEMBER_ID
from mydb.RELATIONSHIP
where child_member_id = HOLD1

UPDATE @TMP
SET HOLD3 = PARENT_MEMBER_ID
from mydb.RELATIONSHIP
where child_member_id = HOLD2

UPDATE @TMP
SET HOLD4 = PARENT_MEMBER_ID
from mydb.RELATIONSHIP
where child_member_id = HOLD3
UPDATE @TMP

SET HOLD5 = PARENT_MEMBER_ID
from mydb.RELATIONSHIP
where child_member_id = HOLD4
select * from @tmp
select
gp4.GROUP_NAME,
gp3.GROUP_NAME,
gp2.GROUP_NAME ,
gp1.GROUP_NAME
from @TMP as tmp
left outer JOIN mydb.GROUP_PROFILE as gp1
on tmp.Hold1 = gp1.MEMBER_ID
left outer JOIN mydb.GROUP_PROFILE as gp2
on tmp.Hold2 = gp2.MEMBER_ID
left outer JOIN mydb.GROUP_PROFILE as gp3
on tmp.Hold3 = gp3.MEMBER_ID
left outer JOIN mydb.GROUP_PROFILE as gp4
on tmp.Hold4 = gp4.MEMBER_ID
[/HTML]

Some explanatory comments would help.
We could read through the code and try and decipher and or guess what you are doing but that would take unnecessary time. So, explanatory comments please!

Regards

Sorry.

I am navigating through a tree structure.

Each "hold" item is a different level on the tree.

I start by feeding the query the serial number of the product I am looking for, then query for all of the parent groups that have this product in it. The product can have multiple parents.

I then go up another level to get the parents of those groups which each only have a single parent.

What I am ultimately trying to do is trace the product to each of it's root level ancestors and tracking all of the ancestors along the way.

Example:

If I have product 2227, it's immediate parents may be:

ABC_account
ABC_sales_account
XZY_Products

Then the final results would be like this.

Customer_accounts/USA_accounts/ABC_account/2227
Sales_accoucts/USA_Sales/ABC_sales_account/2227
Product_portfolio/USA_Products/XYZ_products/22y