SQL Query to Find employees directly linked to manager and have accounts within bank

Question

Im trying to find employees who are supervised by a manager and have accounts in the bank, displaying the branch address that the employee works in and the branch address that the account is opened with?

So far far I have this:

NAME TYPE:

 CREATE TYPE name_type AS object(
 title varchar2(4), 
 firstname varchar2(20), 
 secondname varchar2(20)); 

ADDRESS TYPE:

 CREATE TYPE address_type AS object(
 street varchar2(20),
 city varchar2(20), 
 p_code varchar2(8));

BRANCH TABLE:

CREATE TABLE branches OF branch_type(
branch_ID PRIMARY KEY);

CREATE TYPE BRANCH_TYPE AS OBJECT(
branch_id NUMBER(3),
Address ADDRESS_TYPE);

EMPLOYEES TABLE:

CREATE TABLE employees OF employee_Type(
branch_ID PRIMARY KEY);

CREATE type employee_Type AS object(
branch ref branch_type,
emp_id NUMBER(8),
address ADDRESS_TYPE,
name name_type,
supervisor REF EMPLOYEE_TYPE,
position VARCHAR2(20),
salary   NUMBER(5),
ninum    VARCHAR2(8));

ACCOUNT TABLE:

CREATE TABLE account OF account_type(
acc_num PRIMARY KEY);

CREATE type account_type AS object(
branch_id ref branch_Type,
acc_num       NUMBER(8),
acc_type      VARCHAR(20));

CUSTOMER TABLE:

CREATE TABLE customer OF customer_Type
(cust_ID PRIMARY KEY); 

CREATE type customer_Type AS object(
cust_ID NUMBER(8),
address address_type,
name name_type,
ninum VARCHAR2(8));

CUSTOMER ACCOUNT TABLE:

CREATE TABLE customer_account OF cust_acc_type;

CREATE type cust_acc_Type AS object(
acc_num ref account_Type,
cust_id ref customer_Type);

And here is the query that I'm using.

SELECT          e.name.firstname    as f_name, 
                e.emp_id            as emp_id, 
                m.name.firstname    as manager, 
                e.SUPERVISOR.emp_id as s_id 
FROM            EMPLOYEES   e 
LEFT OUTER JOIN EMPLOYEES   m   ON  e.SUPERVISOR.emp_id = m.emp_id 
WHERE           e.SUPERVISOR.emp_id IS NOT NULL;

The query displays all the employees in the employees table and they're supervisors and the reason I'm also using is not null is because is because the bank managers aren't supervised by anyone Such as below:

| fname | emp_id | supervisor | s_id |
|-------|--------|------------|------|
| john  | 102    | alison     | 101  |
| chris | 106    | john       | 102  |
| ryan  | 108    | chris      | 106  |
| jack  | 804    | loraine    | 802  |

I was thinking of using ninum to display employees with accounts in the bank as this column's values will have matching results from the employees table and the customer table.

I know that John and Jack both have account within the bank. although jack has 2 - 1 current account and 1 savings account. but neither will be displays when I run the query.

SELECT          e.name.firstname       as f_name, 
                e.BRANCH_ID            as emp_id, 
                m.name.firstname       as manager, 
                e.SUPERVISOR.branch_id as s_id, 
                c.cust_id.cust_id      as cust_id 
FROM            EMPLOYEES           e,
                customer_account    c 
LEFT OUTER JOIN EMPLOYEES           m   ON  e.SUPERVISOR.branch_id = m.BRANCH_ID 
WHERE           e.SUPERVISOR.branch_id IS NOT NULL 
AND             c.CUST_ID.ninum = e.ninum;

But I can't figure out how to use ninum to display the branch's address that the employee that work in and the branch address that the account that an employee will have is opened with.

Answer 1

SELECT e.name.firstname                    AS fname,
       e.emp_id,
       e.supervisor.name.firstname         AS manager,
       e.supervisor.emp_id                 AS s_id,
       e.branch.address.street             AS emp_works_at_street,
       e.branch.address.city               AS emp_works_at_city,
       e.branch.address.p_code             AS emp_works_at_pcode,
       ca.acc_num.branch_id.address.street AS acct_at_street,
       ca.acc_num.branch_id.address.city   AS acct_at_city,
       ca.acc_num.branch_id.address.p_code AS acct_at_pcode
FROM   employees e
       INNER JOIN
       customer_account ca
       ON ( e.ninum = ca.cust_id.ninum )
WHERE  e.supervisor IS NOT NULL;