CODEWITHSUNDEEP
sqloracle-databasejoinpowerbuilder
Chihuahua Enthusiast
Chihuahua Enthusiast

Reputation: 1580

Join Table to Another Table then to Itself

I'm using PowerBuilder 12.5 Connected to Oracle 9. I want to select join my employee table to an employee_position table by emp_nbr, then use that emp_nbr to join into the employee table again.

However, I do not want to use the employee_copy table as I did below, since it will be taken down soon. Here's an image that illustrates what I want to do. :

enter image description here

I'm not sure if I should use nested selects or if this is possible only with inner joins. So this SQL code works, and I successfully retrieve the supervisor's name: 

 SELECT "EMPLOYEE"."EMP_NBR",   
         "EMPLOYEE"."DEPT_NBR",   
         "EMPLOYEE"."SHOP",   
         "EMPLOYEE"."LAST_NAME",   
         "EMPLOYEE"."FIRST_NAME",   
         "EMPLOYEE"."MIDDLE_INITIAL",   
         "EMPLOYEE"."EMP_CLASS",   
         "EMPLOYEE_POSITION"."EMP_SUPERVISOR_ID",   
         "EMPLOYEE_COPY"."LAST_NAME",   
         "EMPLOYEE_COPY"."FIRST_NAME",   
         "EMPLOYEE_COPY"."MIDDLE_INITIAL"  
    FROM "EMPLOYEE",   
         "EMPLOYEE_POSITION",   
         "EMPLOYEE_COPY"  
   WHERE ( "EMPLOYEE"."EMP_NBR" = "EMPLOYEE_POSITION"."EMP_NBR" ) and  
         ( "EMPLOYEE_POSITION"."EMP_SUPERVISOR_ID" = "EMPLOYEE_COPY"."EMP_NBR" )

So my question is: How can I do this without using the employee_copy table? Also, this has to be done in one SQL query.

Upvotes: 0

Views: 395

Answers (1)

Mark Stewart
Mark Stewart

Reputation: 2098

No problem: A self-join will work fine:

 SELECT "EMPLOYEE"."EMP_NBR",   
     "EMPLOYEE"."DEPT_NBR",   
     "EMPLOYEE"."SHOP",   
     "EMPLOYEE"."LAST_NAME",   
     "EMPLOYEE"."FIRST_NAME",   
     "EMPLOYEE"."MIDDLE_INITIAL",   
     "EMPLOYEE"."EMP_CLASS",   
     "EMPLOYEE_POSITION"."EMP_SUPERVISOR_ID",   
     "EMPLOYEE_MGR"."LAST_NAME" as mgr_last_name,   
     "EMPLOYEE_MGR"."FIRST_NAME" as mgr_first_name,   
     "EMPLOYEE_MGR"."MIDDLE_INITIAL" as mgr_last_name  
FROM "EMPLOYEE",   
     "EMPLOYEE_POSITION",   
     "EMPLOYEE" EMPLOYEE_MGR  
WHERE ( "EMPLOYEE"."EMP_NBR" = "EMPLOYEE_POSITION"."EMP_NBR" ) and  
     ( "EMPLOYEE_POSITION"."EMP_SUPERVISOR_ID" = "EMPLOYEE_MGR"."EMP_NBR" )

Just use an alias for the EMPLOYEE table of EMPLOYEE_MGR.

Upvotes: 2

Related Questions