0

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

3
  • 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. Commented Mar 17, 2014 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 Commented Mar 17, 2014 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. Commented Mar 17, 2014 at 15:34

2 Answers 2

1

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...

0
SELECT * FROM EMPLOYEES 
WHERE EMPLOYEE_ID IN
(SELECT EMPLOYEE_ID FROM JOB_HISTORY GROUP BY EMPLOYEE_ID HAVING COUNT(EMPLOYEE_ID) >=2);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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