?
Solved

parent-child tree structure in same table

Posted on 2003-12-03
4
Medium Priority
?
720 Views
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

Anthony
 
0
Comment
Question by:whwpzy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:josephfluckiger
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!

joe

0
 

Author Comment

by:whwpzy
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.

Anthony
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 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)
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
 

Author Comment

by:whwpzy
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.

Anthony
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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