#1
  1. No Profile Picture
    Another damn newb...
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    BodÝ, Norway
    Posts
    94
    Rep Power
    13

    Replace IDs with corresponding names


    I have a small tiny problem that I was hoping someone could help me with. Its Oracle... v8

    I have a list of employees. Each employee belong to one workgroup, and each workgroup belong to one department. Each workgroup has got a workgroup leader (w_leader) and each department has got a department leader (d_leader). Both w_leader and d_leader are foreign keys that references EmployeeID. With me so far???

    Now, I have 3 tables:
    Code:
    employee (employeeID, name, departmentID)
    department (departmentID, name, d_leader)
    workgroup (w_leader, employeeID)
    With my select query I want to output:
    ALL EMPLOYEES
    with
    - the name of the employee
    - the name of the employees workgroup leader
    - the name of the employees department leader

    Now, with this query:
    Code:
    SELECT emp.name "EMPLOYEE", wgr.w_leader "WORKGROUP LEADER", dep.d_leader "DEPARTMENT LEADER" 
    FROM emplyoees emp, workgroup wgr, department dep  
    WHERE emp.employeeID = wgr.employeeID 
    AND emp.departmentID = dep.departmentID;
    I end out with this output:

    Code:
    EMPLOYEE   WORKGROUP LEADER   DEPARTMENT LEADER
    ------------------------------------------------
    John    | 1 | 3
    Alice   | 1 | 5
    Mitch   | 2 | 5
    Rather than this:
    Code:
    EMPLOYEE   WORKGROUP LEADER   DEPARTMENT LEADER
    ------------------------------------------------
    John    | Peter   | Ralph
    Alice   | Peter   | Sophie
    Mitch   | Kirsten | Sophie

    I don't manage to output the NAMES of the workgroup and department leaders, only their IDs. How can I get their names out on that list.... ?
    Last edited by torkil; April 27th, 2002 at 11:00 AM.
    Torkil Johnsen

    Never underestimate the power of stupid people in large groups...
    ---------------------------(Ú”,)----
  2. #2
  3. yet another member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    262
    Rep Power
    14
    Hi!

    Well first of all: You knew that you were getting IDs only, right? You said it yourself:
    Both w_leader and d_leader are foreign keys that references EmployeeID
    So you'll need additional joins to get the names for the IDs.

    Code:
    SELECT emp.name "EMPLOYEE", emp2.name "WORKGROUP LEADER", emp3.name "DEPARTMENT LEADER"
      FROM emplyoees emp, workgroup wgr, department dep, emplyoees emp2, emplyoees emp3
     WHERE emp.employeeID = wgr.employeeID
       AND emp.departmentID = dep.departmentID
       AND wgr.w_leader = emp2.employeeID
       AND dep.d_leader = emp3.employeeID;
    I hope I didn't make any serious mistakes here. That query is untested :-)


    Regards,

    Atrus.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)
  4. #3
  5. No Profile Picture
    Another damn newb...
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Location
    BodÝ, Norway
    Posts
    94
    Rep Power
    13
    Ahhh....

    Genious. Additional joins with additional table aliases. Brilliant

    Yes I knew I was only going to get ID's.
    Torkil Johnsen

    Never underestimate the power of stupid people in large groups...
    ---------------------------(Ú”,)----
  6. #4
  7. yet another member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    262
    Rep Power
    14
    Hi!

    "What an honour!" (Zoidberg)

    Atrus.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)

IMN logo majestic logo threadwatch logo seochat tools logo