parent-child tree structure in same table

Posted on 2003-12-03
Last Modified: 2008-02-26
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

Question by:whwpzy
  • 2

Expert Comment

ID: 9868302
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!



Author Comment

ID: 9868736
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.

LVL 50

Accepted Solution

Lowfatspread earned 250 total points
ID: 9868962
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)
        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

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

Author Comment

ID: 9876261
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.


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question