December 24, 2003 at 4:42 am
i have these tables:
Product
-------
CODE CATEGORY_CODE ACCOUNT
P1 C6 ?
P2 C2 ?
each product is contained in a category
for each category there is an account
Category
--------
CATEGORY_CODE ACCOUNT
C1 324
C2 3946
C4 null
C5 3456
C6 456
C7 3946
C8 null
a category can contain a category and a product(like folders and files)
the CategoryRelationship contain the CATEGORY and its parentCATEGORY and the level of relationship
so a category and its mother they have the level of 1,
and a category and its Grand mother they have the level of 2
CategoryRelationship
---------------------
CATEGORY_CODE CATEGORY_Parent Level
C1 C2 1
C1 C4 2
C2 C4 1
C6 C2 1
C6 C4 2
this tree would be like that:
-C4
-C2
-C1
-C6
I WANT TO update (IN ONE an only one update-QUERY) EACH PRODUCT TO HAVE THE ACCOUNT OF THE CATEGORY THE MOST CLOSE TO HIM
FOR EX: P1 WILL GET THE ACCOUNT OF C6 IF IT IS NOT NULL
OR OF C2 IF IT IS NOT NULL AND IF C6 'S ACCOUNT IS NULL
OR OF C4 IF IT IS NOT NULL AND IF C2 AND C6 'S ACCOUNT ARE NULL
I TRIED THIS BUT IT DIDNT WORK
SET ROWCOUNT 1
UPDATE Product
SET Account = Category2.Account
FROM Product INNER JOIN Category ON ....
INNER JOIN CategoryRelationship ....
''''''and level = min(....
INNER JOIN Category Category2 ....
''''''order by level
THANKS
December 24, 2003 at 7:06 am
Unless you exclude (or include in your CategoryRelationship table) any heirarchies greater than two (grandparent), this will not be possible without some form of loop, be it a temorary stack table, cursors, or recursion. I.e., you will not be able to accomplish what you describe in your example (walking the heirarchies past two) in one update query called once for the set.
--Jonathan
--Jonathan
December 28, 2003 at 12:44 am
Won't this work?
Update Product
set P2.Account = Case when C2.Account is not null then C2.Account
else (select C1.Account from Product as P1
inner join Category as C1
on P1.Category_code = C1.Category_code
inner join CategoryRelationship as CR1
on C1.Category_Code = CR1.Category_parent
where CR1.Level = (select min(CR.level) from categoryrelationship as CR
inner join category as C
on C.Category_code = CR.Category_parent
where CR.Category_Code = P2.Category_Code
and C.Account is not null)
and CR1.Category_Code = P2.Category_code
and C1.Account is not null )
end) from Product P2 inner join Category C2
on P2.Category_code = C2.Category_code
December 29, 2003 at 6:32 am
quote:
Won't this work?Update Product
set P2.Account = Case when C2.Account is not null then C2.Account
else (select C1.Account from Product as P1
inner join Category as C1
on P1.Category_code = C1.Category_code
inner join CategoryRelationship as CR1
on C1.Category_Code = CR1.Category_parent
where CR1.Level = (select min(CR.level) from categoryrelationship as CR
inner join category as C
on C.Category_code = CR.Category_parent
where CR.Category_Code = P2.Category_Code
and C.Account is not null)
and CR1.Category_Code = P2.Category_code
and C1.Account is not null )
end) from Product P2 inner join Category C2
on P2.Category_code = C2.Category_code
I was assuming, perhaps incorrectly, that you needed the heirarchy tree "walked" if both the parent and grandparent categories were null. I based this assumption partly on the fact that your sample data otherwise evaluates to all NULLs. If not, then perhaps something like this:
SELECT r.Code, COALESCE(cr.Account, cp.Account, cg.Account)
FROM Product r LEFT JOIN Category cr ON cr.Category_Code = r.Category_Code
LEFT JOIN CategoryRelationship p ON p.Category_Code = r.Category_Code AND p.Level = 1
LEFT JOIN CategoryRelationship g ON g.Category_Code = r.Category_Code AND g.Level = 2
LEFT JOIN Category cp ON cp.Category_Code = p.Category_Parent
LEFT JOIN Category cg ON cg.Category_Code = g.Category_Parent
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply