April 1, 2008 at 10:15 am
Hi All,
Following are the schema details
EMPLOYEE( #EmpId, Ename, Salary, #ManagerId(EmpId), #DeptId)
DEPERTMENT (#DeptId, DeptName, #DeptHead(EmpId))
SELECT Employee Name, Manager Name, Department name, Department Head Name for all employees, with out using three instances of EMPLOYEE table.
can anybody help me out in this. At least tell me is it possible or not.
below is the query that i tried.
SELECT
E1.Ename, E2.Ename AS [MgrName], T.DeptName, T.Ename AS [Dept Head Name]
FROM Employee E1
INNER JOIN ( SELECT D.DeptId, D.DeptName, E.Ename FROM Department D INNER JOIN Employee E ON E.EmpId = D.DeptHead) AS T
ON T.DeptId = E1.DeptId
LEFT JOIN Employee E2
ON E2.EmpId = E1.ManagerId
Regards
Enbee
Regards | Enbee
April 1, 2008 at 10:37 am
April 1, 2008 at 10:42 am
This works just fine....
DECLARE @EMPLOYEE TABLE (EmpId int, Ename varchar(20), Salary money, ManagerId int, DeptId int)
DECLARE @DEPaRTMENT TABLE (DeptId int, DeptName varchar(100), DeptHead int)
INSERT INTO @Employee
SELECT 1, 'John', 0, 0, 1 UNION ALL
SELECT 2, 'Bob', 0, 1, 1 UNION ALL
SELECT 3, 'Tom', 0, 1, 1 UNION ALL
SELECT 4, 'Mary', 0, 1, 2 UNION ALL
SELECT 5, 'Nancy', 0, 4, 2
INSERT INTO @Department
SELECT 1, 'DBA Group', 1 UNION ALL
SELECT 2, 'Database Developers', 1
SELECT E1.Ename as [Employee Name],
E2.Ename as [Manager Name],
E3.Ename as [Department Head]
FROM @Employee E1
LEFT JOIN @Employee E2
ON E1.ManagerID = E2.EmpID
LEFT JOIN @Department D
ON E1.DeptID = D.DeptID
LEFT JOIN @Employee E3
ON D.DeptHead = E3.EmpID
April 1, 2008 at 10:44 am
yeah, the phrasing of your post sounds like homework. i'm not sure what's considered an 'instance', but the employee table will indeed be referenced 3 times even if it's hidden in a CTE.
April 1, 2008 at 11:06 am
Enbee (4/1/2008)
can anybody help me out in this. At least tell me is it possible or not.
Yes, it is possible with a CTE (assuming you mean code references).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 11:48 am
Enbee: look at the topic titled "Recursive Queries Using Common..." in SQL Server 2005 Books Online and take better notes in class. 😀
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply