Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

parent-child tree structure in same table

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

597 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