Good Morning
i am using Oracle version 11.0.2.0.3
I have three tables, PROJECT, HIERARCHY, and HR.
I need to send an email to all of the employees that are associated with the project. The email will go to the PROJECT_MANAGER and the email will be CC: to the employee to the left and the right of the PROJECT_MANAGER in the HIERARCHY table (using the ID#). I am having problem updating my existing query to retrieve all of contact information and email addresses from the HIERARCHY table to be used for the email.
Here are the tables.
PROJECT table
CREATE TABLE "STUDENT"."PROJECTS"
( "PROJECT_ID" NUMBER,
"PROJECT_NAME" VARCHAR2 (100 BYTE),
"PROJECT_MANAGER" NUMBER,
"PROJECT_MANGER_SUPV" NUMBER
)
| PROJECT_ID | PROJECT_NAME | PROJECT_MANAGER | PROJECT_MANAGER_SUPV |
| 1000 | Children's Arts and Craft | 950 | 852 |
| 1001 | Children's Science Club | 951 | 855 |
| 1002 | Children's Math Club | 952 | 859 |
| 1003 | Children's Dance Club | 953 | 856 |
| 1004 | Children's Drama Club | 954 | 851 |
| 1005 | Children's Music Club | 955 | 853 |
| 1006 | Children's Science and Math Club | 956 | 855 |
| 1007 | Children's Dance and Drama Club | 957 | 851 |
HIERARCHY table
CREATE TABLE "STUDENT"."HR"
( "EMPLOYEE_ID" NUMBER,
"EMPLOYEE_FIRST_NAME" VARCHAR2(20 BYTE),
"EMPLOYEE_LAST_NAME" VARCHAR2(20 BYTE),
"EMPLOYEE_EMAIL" VARCHAR2(50 BYTE)
)
| ID_0 | ID_1 | ID_2 | ID_3 | ID_4 | ID_5 | ID_POSITION |
| 950 | 950 | 852 | 739 | 625 | 543 | 1 |
| 951 | 951 | 855 | 734 | 627 | 541 | 1 |
| 952 | | 952 | 859 | 627 | 541 | 2 |
| 953 | | 953 | 856 | 625 | 541 | 2 |
| 954 | | | 954 | 851 | 543 | 3 |
| 955 | 955 | 853 | 739 | 625 | 543 | 1 |
| 956 | 956 | 855 | 734 | 627 | 541 | 1 |
| 957 | 957 | 851 | 739 | 625 | 543 | 1 |
HR table
CREATE TABLE "STUDENT"."HIERARCHY"
( "ID_0" NUMBER,
"ID_1" NUMBER,
"ID_2" NUMBER,
"ID_3" NUMBER,
"ID_4" NUMBER,
"ID_5" NUMBER,
"ID_POSITION" NUMBER
)
Here is my query to get the PROJECT_MANAGER contact information and email from the HIERARCY and HR tables
SELECT
PROJECTS.PROJECT_ID,
PROJECTS.PROJECT_NAME,
PROJECTS.PROJECT_MANAGER,
HR.EMPLOYEE_FIRST_NAME,
HR.EMPLOYEE_LAST_NAME,
HR.EMPLOYEE_EMAIL,
HIERARCHY.ID_0,
HIERARCHY.ID_1,
HIERARCHY.ID_2,
HIERARCHY.ID_3,
HIERARCHY.ID_4,
HIERARCHY.ID_5,
HIERARCHY.ID_POSITION
FROM HR,
PROJECTS,
HIERARCHY
WHERE PROJECTS.PROJECT_MANAGER = HIERARCHY.ID_0
AND PROJECTS. PROJECT_MANAGER = HR.EMPLOYEE_ID
Here are the results of the above query.
Now, the issue that I am having is that I need to retrieve the EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME, and EMPLOYEE_EMAIL for all of employees associated to the project. So using the existing query, I need to update the query the information from the HR table for ID_1, ID_2, ID_3, ID_4, and ID_5.
Here are the expected results: