Oracle: WITH-clause querying two tables-CodePudding

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.

CodePudding user response：

The SQL WITH clause creates 'virtual' tables which you can reference in later queries under the clause. These tables exist for the lifetime of the query in memory. They are a bit like views.

Your example is not working as you are establishing a virtual table called find_emp, but then you are not using it.

An example

WITH subquery AS (
SELECT col1 , col2, col3 
FROM table1
WHERE col4=condition1
)

SELECT * FROM subquery;

Hope that helps.

CodePudding user response：

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)