Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently using the 'hr' database with Oracle 10g. You can view the schema diagram over here: http://docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63

What I would like is to retrieve the history of all employees who have worked in the 'Sales' department. I have selected only first name, last name, and job title for the working example but I'm getting the same job title where I should be getting two different ones.

This is the query I have so far:

SELECT a.first_name, a.last_name, b.job_title
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND a.job_id = b.job_id
AND c.department_id = d.department_id;

Any help would be appreciated. JazakAllah khayr.

share|improve this question
    
Why do you use WHERE d.department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales') instead of a simple WHERE department_name = 'Sales'? –  dezso Aug 27 '13 at 16:24
    
I've been doing so many of these that I didn't think twice about that. I'm helping a friend make sample questions for an introductory class. Thanks. –  Khalid Hussain Aug 27 '13 at 16:30

3 Answers 3

An introduction to SQL queries but better you look for an introductory SQL text

I assume that every employee is in the job_history table and every employee and therefore job is assigned to a department. Otherwise one will loose some employees.

Let's display the columns of the tables involved:

select employee_id,first_name,last_name,department_id
from employees;

select department_id, department_name
from departments;

select job_id,employeed_id,department_id
from job_history;

select job_id,job_title
from jobs;

First a list of all employees and their departement information

select *
from employee e, departments d
where e.department_id=d.department_id;

now select only the rows from the employees that are in the department 'Sales'

select *
from employee e, departments d
where e.department_id=d.department_id
and d.department_name='Sales';

for these employees now find the jobs have already head fron the job_history

select *
from employee e, departments d, jobhistory h
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id;

Now for each job find the job title

select *
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

Now we display only the columns we need

select e.first_name,e.last_name,j.job_title
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

If we want to know in which department they worked in their job wee join another department table to get these information

select e.first_name,e.last_name,j.job_title,d2.department_name
from employee e, departments d, jobhistory h, jobs j,departments d2
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id
and h.depatment_id=d2.department_id;
share|improve this answer
    
Great Explanation..!!! –  Dba Aug 28 '13 at 9:24

I've figured out the original problem. I should have constricted job_id from job_history and jobs instead of the original employees and jobs tables.

This would be the final answer:

SELECT a.employee_id, a.first_name, a.last_name, b.job_title, d.department_name
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND c.job_id = b.job_id
AND c.department_id = d.department_id;
share|improve this answer

A better answer, using ANSI Join syntax, which is significantly clearer:

SELECT e.employee_id, e.first_name, e.last_name, j.job_title, d.department_name
  FROM departments d
       inner join job_history jh
          on d.department_id = jh.department_id
       inner join employees e
          on jh.employee_id = e.employee_id
       inner join jobs j
          on jh.job_id = j.job_id
WHERE d.department_name = 'Sales';

The ANSI syntax helps prevent accidental cartesian products and clearly differentiates join criteria from filter criteria.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.