Self Join Table and Tree Result Query
-
Dear All,
I need your input concerning the following situation. I have an Employee table that has Manager column. This column self refernces employee table i.e the manager of the employee is another employee.
I need to display some kind of organization chart i.e. list of employees ordered according to their hierachy in the organization.
Example
Employee Manager
------------------
1
-
try something like this, now it really depends on how your table is built, this is just my assumption and to be taken as is without any warrantly that i understood everything
SET NOCOUNT ON
DECLARE @EMP TABLE
(
RowNum int IDENTITY (1,1),
EMPID varchar(20),
EMPNAME varchar(30),
MANAGERID varchar(20)
)
INSERT INTO @EMP (EMPID, EMPNAME, MANAGERID) VALUES (1, 'ZYRUS', 0)
INSERT INTO @EMP (EMPID, EMPNAME, MANAGERID) VALUES (2, 'NARUTO', 1)
INSERT INTO @EMP (EMPID, EMPNAME, MANAGERID) VALUES(3, 'SASUKE', 1)
INSERT INTO @EMP (EMPID, EMPNAME, MANAGERID) VALUES(4, 'XIEN', 0)
SELECT A.MANAGERID, A.EMPID, A.EMPNAME AS MANAGER,
B.EMPNAME AS STAFF_NAME, B.EMPID AS STAFF_ID FROM @EMP A
LEFT OUTER JOIN @EMP B ON A.EMPID = B.MANAGERID
--try the following select statements
SELECT A.MANAGERID, A.EMPID, A.EMPNAME AS MANAGER,
B.EMPNAME AS STAFF_NAME, B.EMPID AS STAFF_ID FROM @EMP A INNER JOIN @EMP B ON A.EMPID = B.MANAGERID
--hope this helps anyway
-
The point is I want the order of the result set to be the order of employees in the hierachy if employee A is managed by employee B, Employee B is managed by employee C. Then I want the order to be C , B and then A
-
Please post your tables and an their relationship.
Thanks
Mike
-
I have found reference to http://sqljunkies.com/WebLog/ktegels/archive/2005/05/11/14179.aspx in SQL server daily newsletters but I do not want the output for xml as example shows. I want result set ordered by hierachy of employees as simple order by clause. Do I have to create a function that orders the records in temp table and then work on the table or is there any other solution?
Anyway the table and the relationships are very simple as follows
CREATE TABLE [dbo].[Employee] (
[EM_ID] [char] (15) NOT NULL ,
[EM_Name] [char] (50) NULL ,
[EM_Manager] [char] (15) NULL ,
[ROWID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EM_ID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [FK_Employee_Employee] FOREIGN KEY
(
[EM_Manager]
 
REFERENCES [dbo].[Employee] ([EM_ID]
 
GO
-
Add a group by to Bersileus's statement.
SELECT A.MANAGERID, A.EMPID, A.EMPNAME AS MANAGER,
B.EMPNAME AS STAFF_NAME, B.EMPID AS STAFF_ID FROM @EMP A INNER JOIN @EMP B ON A.EMPID = B.MANAGERID
GROUP BY B.MANAGERID
HTH Mike
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply