parent-child tree structure in same table

Hi there,

I have two tables. One is issues table like every user can send issue to database. Another table is user table. The outline like this.

User table:

UserID   Username   Password     ManagerID
 1           user1          pass1           0
 2           user2          pass2           1
 3           user3          pass3           2
 4           user4          pass4           3
......

Issue Table

IssueID   Issue         UserID          Date
 1            test            3                01/02/03
 2            test1          4                 02/05/03
 3            test 2         2                 03/09/03
...

In User table, managerID is the foreignkey of userID. My problem is that I want to find a effient way to create parent-child tree structrue. So UserID 1 can see all issues he and his employees sent, so he can see issue 1, 2, 3. But for UserID4, he can only see issue 2.
Currently, I only have a solution using complicated stored procedure which query embed into query and if the level go beyond 3, I can't handle it.

Can anyone help me out?

Thanks a lot

Anthony
 
whwpzyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

josephfluckigerCommented:
So in your sample UserID table, the manager for 2 is 3 and the manager of 3 is 2, correct? And you want everyone to see their own Issues, and the issues of everyone who works underneath them, correct?

How about creating another column in the User table called "underlings" that lists out the UserID of everyone who works for them separated by commas.

UserID      ManagerID                      password                      underlings (includes their own ID)
1      0            pass1            "1,2,3,4"
2      1            pass2            "2,3,4"
3      2            pass3            "3,4"
4      3            pass4            "4"


Then you can follow these steps:
-a User logs in
-lookup the underlings value for this user, and store them in a variable Unders
-run the following query:

SELECT IssueID, Issue, UserID, date
FROM tblIssue WHERE ((InStr(1,Unders,[UserID])>=1));

So for example when UserID 2 logs in this query will read:

SELECT IssueID, Issue, UserID, date
FROM tblIssue WHERE ((InStr(1,"3,4",[UserID])>=1));

(syntax may vary based on application server language)


You now have to manage the underlings field every time a user changes or gets promoted, but this may be more efficient and perhaps simpler than what you have. It's certainly a fast query once you get the underlings field of the User table populated.

Hope that helps!

joe

0
whwpzyAuthor Commented:
Hi Joe,

Thanks for your input. But that's not what I am looking for. There must be some way to do it more efficient.

Anthony
0
LowfatspreadCommented:
Not really this is an alternative...

create table #t (tid int identity(1,1) , level int ,Userid int)

declare @lvl int

insert into #t (level, userid)
 select @lvl,userid from usertable
 
while exists (select tid from #t where Level=@lvl)
begin
        set @lvl=@lvl+1  
      Insert into #t (level,userid)  
           Select @lvl,u.Userid from #t inner join usertable as u
            on u.managerid = #t.userid
            where level=@lvl-1
end

select i.* from issue i inner join #t on i.userid = #t.userid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
whwpzyAuthor Commented:
Hi Lowfatspread,

Sorry for my late response. I have to workin gon other projects at same time. Your solution works fine after I made a small change.
There should be a initial value ofr @lvl

select @lvl=1

But I already get your idea and really appreciate your help.

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.