By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

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

Lokean
P: 71
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
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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
P: 71
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.