Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Unanswered: Semi Complex Query help

    I am looking for help on a query. I have 2 tables. One is a generic contact infortmation table, the other is data.

    So here is an easy way to explain it. All the employees of a company are listed in the contact info table with name, address, phone, etc.
    The second table is used to start building an org structure.


    For instance. I want to query the following:

    EmployeeID, FirstName, LastName, ManagerID, FirstName LastName.

    The problem I am always running into is the First and Last name is always the same person. I cannot figure out the SQL to get the First and Last name of the manager to populate. Essentially I know it is something to do with the join but I am just not expereinced enough to know how to build the right query.

    Code:
    SELECT EmployeeTbl.EmployeeId, ContactInfoTbl.FirstName, ContactInfoTbl.LastName, EmployeeTbl.Status, EmployeeTbl.MgrId, ContactInfoTbl.FirstName, ContactInfoTbl.LastName
    
    FROM ContactInfoTbl INNER JOIN EmployeeTbl ON ContactInfoTbl.PersonID = EmployeeTbl.Employeed
    
    WHERE (((EmployeeTbl.Status)="FT"));
    Any help is appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must create an Auto-Join (or Self-Join), like this:

    Code:
    SELECT EmployeeTbl.EmployeeId, 
           EmployeeTbl.Status, 
           ContactInfoTbl.FirstName, 
           ContactInfoTbl.LastName, 
           EmployeeTbl.ManagerID, 
           ContactInfoTbl_M.FirstName, 
           ContactInfoTbl_M.LastName
     FROM (ContactInfoTbl INNER JOIN EmployeeTbl ON ContactInfoTbl.PersonID = EmployeeTbl.EmployeeId) 
                          INNER JOIN ContactInfoTbl AS ContactInfoTbl_M ON EmployeeTbl.ManagerID = ContactInfoTbl_M.PersonID
     WHERE EmployeeTbl.Status="FT";
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    7

    Thank you

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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