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

On 4 Apr 2005 15:04:10 -0700, HL5138 (AT) gmail (DOT) com wrote:

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)

On Tue, 05 Apr 2005 10:00:37 +0200, Hugo Kornelis wrote:

And if that fails, try googling for the same search phrase in the group
microsoft.public.sqlserver.programming. I'm not sure if it's been
discussed here, but I do know it has been in that group - numerous
times.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

1) Look at the nested sets model for hierarchies or buy my book TREES
& HIERARCHIES IN SQL.

2) Do not use dynamic SQL, cursors or other procedural code in SQL
unless you ABSOLUTELY have to do so.