On 4 Apr 2005 15:04:10 -0700,
HL****@gmail.com wrote:
I have a department table like this:
DeptID Department ParentID, Lineage
1 HR NULL (
2 Temp1 1 (1,
3 Temp2 2 (1,2
4 PC NULL (
I have a deptmember table like this:
DeptID MemberID IsManager
1 1 Y
4 1 Y
I need to query table to get all department belong to MemberID 1 with
all children departments.
My thought is:
1. Do Select * from deptmember where MemberID=1 and IsManager=Y
2. Loop thru this table to build SQL
Where Lineage like '%1' OR Lineage like '%4'
3. Select * from department using where statement from step 2.
How do you loop thru results from step1, Do I need to use a cursor?
Thanks,
HL
Hi HL,
You should not loop at all. Instead, do it all in one set-based
operation:
SELECT d.DeptID, d.Department, -- better not to use
d.ParentID, d.Lineage -- SELECT * in prod code!!
FROM DeptMember AS dm
INNER JOIN Department AS d
ON d.Lineage LIKE '(' + STR(dm.DeptID) + ',%'
OR d.Lineage LIKE '%,' + STR(dm.DeptID) + ',%'
WHERE dm.MemberID = 1
AND dm.IsManeger = 'Y'
Note that I also changed the LIKE statement a bit. Maybe you need to
modify it further to accomodate your needs. Just using LIKE '%1' as you
posted would match no rows at all; LIKE '%1%' would work, but it would
match rows with Lineage '(13,7,5' as well (due to the 1 in 13).
Final note: you might wish to google this group for "nested sets model",
to see a different approach to storing hierarchies in an RDBMS that many
people prefer.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)