Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Select within Select ?

    Hi folks,

    I need help with the following select to see if it's possible

    I am able to pull a list of data from our ERP system like below.

    Select EmpID, Name, ManagerID from erp

    Emp ID | Name | ManagerID
    A1234 | Joe Bloggs | A1234
    A9999 | Jane Bloggs | A1234

    Note Joe Bloggs ManagerID is actually himself and Joe is also Jane's manager. I'm trying to create another column to have the Managers name populated so my output is like the following

    Emp ID | Name | ManagerID | Manager Name
    A1234 | Joe Bloggs | A1234 | Joe Bloggs
    A9999 | Jane Bloggs | A1234 | Joe Bloggs

    Any pointers or help appreciated.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    Select EmpID, Name, ManagerID
       FROM erp AS emp
       LEFT OUTER JOIN erp AS boss
          ON boss.EmpId = emp.ManagerID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks Pat. I'm a bit lost though. Am i going in the right direction

    SELECT EmpID, Name, ManagerID,
    (SELECT EmpID, Name, ManagerID
    FROM
    ERP AS emp
    LEFT OUTER JOIN
    ERP AS boss ON boss.EmpID = emp.ManagerID) AS ManagerName
    FROM ERP

  4. #4
    Join Date
    Oct 2009
    Posts
    93
    Also, this is on a view of ERP Database. Adding additional column in result won't result in any change to underlying schema or anything ? if yes, then i'll abort

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This should work "as is":
    Code:
    SELECT emp.EmpID, emp.Name, emp.ManagerID, boss.Name
       FROM erp AS emp
       LEFT OUTER JOIN erp AS boss
          ON boss.EmpId = emp.ManagerID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Oct 2009
    Posts
    93
    no luck Pat. getting invalid column name 'name' error

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @erp TABLE (
       EmpID        VARCHAR(9)      NOT NULL
    ,  Name         VARCHAR(25)     NOT NULL
    ,  ManagerID    VARCHAR(9)      NOT NULL
    )
    
    INSERT INTO @erp VALUES
       ('A1234', 'Joe Bloggs',  'A1234')
    ,  ('A9999', 'Jane Bloggs', 'A1234')
    
    SELECT emp.EmpID, emp.Name, emp.ManagerID, boss.Name
       FROM @erp AS emp
       LEFT OUTER JOIN @erp AS boss
          ON boss.EmpId = emp.ManagerID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2009
    Posts
    93
    Many thanks Pat... My column name was different to what i posted and i never copped it

    much appreciated

    hope all things are good with you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •