467,915 Members | 1,265 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

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

Lokean
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]
Oct 2 '08 #1
  • viewed: 911
Share:
2 Replies
Delerna
Expert 1GB
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
Oct 2 '08 #2
Lokean
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
Oct 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Mary Ellen Curtin | last post: by
6 posts views Thread by sheffdog | last post: by
3 posts views Thread by Pierre Saint-Jacques | last post: by
12 posts views Thread by Santosh | last post: by
44 posts views Thread by Andy Dingley | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.