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 ) OUTER;
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) )
INNER
, that's a reserved word. And theOUTER
alias, remove it. That's not a derived table and does not need an alias.HAVING cnt >= 2
should beHAVING COUNT(*) >= 2
, as in Adel's answer.