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

  • 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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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