Trouble understanding inner join

Question

Im trying to understand inner join correct use but im having trouble when I have to call more than 2 tables, im using oracle HR schema for tests, so for example im trying this query:

select emps.employee_id,
       emps.first_name,
       dep.department_name,
       dep.department_id,
       jh.job_id
from   employees emps
       inner join departments dep
       on emps.department_id = dep.department_id
       inner join job_history jh
       on dep.department_id = jh.department_id;

which shows the following result:

I know its wrong because first its showing duplicate rows, and second if I run this query

select * from job_history where employee_id = 100;

It shows no results which means that employee 100 (steven) shouldnt be appearing in the results of the first query, I expect first query to show me the results of data that is on employees and also on department and also on job_history which have in common the department_id

HR (human resources schema):

Can anyone help me, what im missing, why it shows duplicate rows?

Answer 1

The job_history table has 3 foreign keys:

1. employee_id
2. job_id
3. department_id

Your INNER JOIN only joins on the DEPARTMENT_ID so you are matching an employee to the job history for any jobs that occurred for their department but not necessarily jobs specific to that employee. Instead, you probably want to join on either the employee_id or the combination of employee_id and department_id.

So, if you want the jobs history for that employee for any department:

select emps.employee_id,
       emps.first_name,
       dep.department_name,
       dep.department_id,
       jh.job_id
from   employees emps
       inner join departments dep
       on ( emps.department_id = dep.department_id )
       inner join job_history jh
       on ( emps.employee_id = jh.employee_id );

Or, if you want the job history for that employee within that department:

select emps.employee_id,
       emps.first_name,
       dep.department_name,
       dep.department_id,
       jh.job_id
from   employees emps
       inner join departments dep
       on ( emps.department_id = dep.department_id )
       inner join job_history jh
       on ( emps.employee_id = jh.employee_id
          and emps.department_id = jh.department_id );