Search code examples
sqloracle-databasesubquerywith-statement

Oracle: WITH-clause querying two tables

Using an Oracle database, I have two tables:

Employees:

Employee_id | Number(6,0)
Last_name   | Varchar2(20)
Hire_date   | Date
Deparment_id| Number(4,0)

Job_history:

Employee_id  | Number(6,0)
Start_date   | Date
Deparment_id | Number(4,0)

I am supposed to find - using the WITH-clause - all the employees who currently work in the same department where they started to work (hire_date = start_date and same department_id). I easily got the right result using a JOIN in the subquery:

SELECT DISTINCT e.employee_id, e.last_name, e.hire_date, 
e.department_id as current_dep, j.department_id as prev_dep
FROM hr.employees e 
JOIN (SELECT employee_id, department_id, end_date, start_date 
      FROM hr.job_history ) j
ON e.employee_id = j.employee_id
WHERE e.department_id = j.department_id;

(Right) OUTPUT:

Unfortunately with the WITH-clause I am getting troubles, since I am not sure how to manage two different tables (most examples I found on the web are just with one table)

 --best try until now--

With find_emp as (SELECT hire_date, department_id 
                    FROM hr.employees)
SELECT e.employee_id, e.last_name, e.department_id as curr_dep
FROM HR.employees e
WHERE e.hire_date IN (SELECT j.start_date
                      FROM hr.job_history j
                      JOIN hr.employees e
                      ON e.employee_id = j.employee_id);

(wrong) OUTPUT:

What I am doing wrong? Since I am new to SQL, I would appreciate every hint. Thank you very much in advance.

Solution

  • Should be something among the lines:

    WITH start_dept AS
(
  SELECT emp.employee_id, dept.deparment_id AS prev_dep
    FROM employees   emp
       , job_history dept
   WHERE emp.employee_id = dept.employee_id
     AND emp.hire_date   = dept.start_date
)
SELECT e.employee_id, e.last_name, e.hire_date, e.deparment_id AS current_dep, sd.prev_dep
  FROM employees  e
     , start_dept sd
 WHERE e.employee_id  = sd.employee_id
   AND e.deparment_id = sd.prev_dep;

    (assuming Employees.deparment_id is the current departmen and Employees.hire_date matches the start_date from Job_history)