Solved

parent-child tree structure in same table

Posted on 2003-12-03
4
717 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 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)
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

729 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