Skip to content
Advertisement

Oracle: WITH-clause querying two tables

Using an Oracle database, I have two tables:

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:

(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)

(wrong) OUTPUT:

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

Advertisement

Answer

Should be something among the lines:

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement