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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying the following query on the tables which are described below:

SELECT * FROM job_history INNER JOIN employees
ON job_history.employee_id = employees.employee_id
WHERE employee_ID IN
    (SELECT employee_id FROM

        (SELECT employee_id, COUNT(employee_id) AS cnt
         FROM job_history
         GROUP BY employee_id
         HAVING cnt >= 2) INNER

But I get an error in MySQL, that every derived table must have its own alias

We have a table job_history(employee_id, start_date, end_date, job_id, department_id) containing the following data:

'102', '1993-01-13', '1998-06-24', 'IT_PROG', '60'
'101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'
'101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'
'201', '1996-02-17', '1999-12-19', 'MK_REP', '20'
'114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'
'122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'
'200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'
'176', '1998-03-24', '1998-12-31', 'SA_REP', '80'
'176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'
'200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90'

We have another table employees(employee_id int, first_name varchar(20), last_name varchar(25) not null, email varchar(25) not null, phone_number varchar(20), hire_date date not null, job_id varchar(10) not null, salary float, commission_pct float, manager_id int, department_id int, constraint emp_pk primary key(employee_id) )

share|improve this question
Don't name your derived table with INNER, that's a reserved word. And the OUTER alias, remove it. That's not a derived table and does not need an alias. – ypercubeᵀᴹ Mar 17 '14 at 9:27
Yes thank you, its working fine without strict FULL GROUP BY MODE. It is not working with the FULL GROUP BY mode on @ypercube – user2493976 Mar 17 '14 at 9:32
I can't see why that mode would have anything to do with this query. Perhaps the HAVING cnt >= 2 should be HAVING COUNT(*) >= 2, as in Adel's answer. – ypercubeᵀᴹ Mar 17 '14 at 15:34
up vote 0 down vote accepted

Your Query should be like:

SELECT * FROM job_history INNER JOIN employees 
ON job_history.employee_id = employees.employee_id
WHERE job_history.employee_ID IN
    (SELECT employee_id
         FROM job_history
         GROUP BY employee_id
         HAVING COUNT(employee_id) >= 2

so you missed the alias before the id "WHERE job_history.employee_ID IN..", and the where in change it to what above...

share|improve this answer

Your Answer


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.