QUERY help

  • 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

  • Why can't you have 3 instances of the Employee table? Is this homework?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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.

  • 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]

  • 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