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

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 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 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 at 15:34

1 Answer 1

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

 
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.