[quoted text, click to view]

Don't use Sqlect tiop 100 percent . It will be deprecated.
As a quick solution . I think you have managerid in your table along
with id . Not tested

SELECT eu.ID, eu.FirstName, eu.LastName, eu.Supervisor
supervisor.id as SupervisorID, supervisor.firstname as
SupervisorFirstName,
supervisor.lastname as SupervisorLastName, NextLevelManager.id as
NextLevelManagerID,
NextLevelManager.firstname as NextLevelManagerFirstName,
NextLevelManager.lastname as NextLevelManagerLastName

FROM EndoscopyUser.dbo.EndoscopyUser eu
LEFT OUTER JOIN EndoscopyUser.dbo.EndoscopyUser supervisor
ON eu.managerid = supervisor.id
LEFT OUTER JOIN EndoscopyUser.dbo.EndoscopyUser nextlevelmanager
ON supervisor.managerid = nextlevelmanager.id

ORDER BY LastName

Hi,

I have a query which goes like this:

SELECT TOP 100 PERCENT ID, FirstName, LastName, Supervisor
FROM EndoscopyUser.dbo.EndoscopyUser eu
ORDER BY LastName

Every Supervisor will also have an entry in the same table with their ID,
FirstName, LastName, Supervisor. I would like to know how I can show upto 2
levels high.

For instance, in 1 row, I see my name, my immediate manager, and my next
level manager (i.e., my immediate's manager's manager)

ID, FirstName, LastName, Supervisor, SupervisorID, SupervisorFirstName,
SupervisorLastName, NextLevelManagerID, NextLevelManagerFirstName,
NextLevelManagerLastName

Can I query again within the same table?

Thanks.

--
---------------------
zwieback89

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200705/1

Before going to a nested structure algorithim, I was wondering if I can
achieve my results in another way. Here is the DDL with some test data.

Srinivas,
Unfortunately the table is not perfect. Each row has a Supervisor, but not
Supervisor ID.

DDL is:

CREATE TABLE Test (
LawsonID varchar(20) primary key,
FirstName varchar(100),
LastName varchar(100),
Supervisor varchar(100)
)

INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('10956',
'Ali','Chowdhury','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('14533',
'Bradley','Rosintoski','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('18778',
'Brian','Emmett','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('21337',
'Sathiya','Rajagopal','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2276',
'Aravind','Seshadri','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2756',
'Polar','Huang','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T951',
'Rishi','Kabra','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2396',
'Johnson','Abraham','Chowdhury, Ali')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T716',
'Kevin','Schmidt','Chowdhury, Ali')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('24990',
'Purna','Pidaparti','Ileana Rivera')

So the Org Structure is:

Ileana Rivera
Purna Pidaparti
Sathiya Rajagopal
Ali Choudhury

Sathiya and Ali are managers too with people under them.

So using this data, is it possible to get

LawsonID, FirstName, LastName, Supervisor, SupervisorID, SupervisorFirstName,
SupervisorLastName, NextLevelManagerID, NextLevelManagerFirstName,
NextLevelManagerLastName

A supervisor is also an employee, his LawsonID is found within the same table
too.

Thanks.

--
Message posted via http://www.sqlmonster.com

I tried to build a stored procedure which would get my results. It is still
in the intial stage:

Alter Procedure BuildOrganization1

AS

Declare @minLawson varchar
Set @minLawson = (Select min(LawsonID) from Test)

INSERT INTO BuildOrganization(LawsonID,FirstName,LastName,
DirectManagerLawsonID,DirectManagerFirstName,DirectManagerLastName,
NextLevelManagerLawsonID,NextLevelManagerFirstName,NextLevelManagerLastName)
SELECT @minLawson, FirstName, Lastname, NULL, NULL, NULL, NULL, NULL, NULL
FROM Test
Where LawsonID = @minLawson

GO

What I find it strange is that when I execute this part:

(Select min(LawsonID) from Test)

It returns me a minimum LawsonID. (i.e., 1 row)

But when I try to run this part:

Declare @minLawson varchar
Set @minLawson = (Select min(LawsonID) from Test)

--INSERT INTO BuildOrganization(LawsonID,FirstName,LastName,
DirectManagerLawsonID,DirectManagerFirstName,DirectManagerLastName,
NextLevelManagerLawsonID,NextLevelManagerFirstName,NextLevelManagerLastName)
SELECT @minLawson, FirstName, Lastname, NULL, NULL, NULL, NULL, NULL, NULL
FROM Test
Where LawsonID = @minLawson

It returns me no rows....

Why would that be so?

THanks.

--
---------------------
zwieback89

Message posted via http://www.sqlmonster.com