I need to find the department that has the oldest employee. My plan is to merge everything into one big table and try to sort the employees by age, then find the department that has the oldest employee, and find the manager for that department. Currently this is my query:

  FROM employees emp 
  JOIN dept_emp de       ON emp.emp_no=de.emp_no 
  JOIN departments dep   ON de.dept_no=dep.dept_no 
  JOIN dept_manager depm ON dep.dept_no=depm.dept_no 
  JOIN employees emp2    ON depm.emp_no=emp2.emp_no
-- some part of query is lost here -- 
 LIMIT 0, 1


Unfortunately, when I run that, it's saying that column 'birth_date' is ambiguous to sort - which makes sense bc I'll have 2 columns with the same date.

  • You have the ambiguity because you have joined employees twice and both instances have the same set of columns. Therefore you have to use SELECT emp.*, de.*, dep.* ... instead of plain SELECT * – Kondybas Oct 14 '18 at 15:03
  • You seem to have many:many mapping tables in places where you need only 1:many relationships? Or, can an emp be in several depts? And a dept can have several managers? – Rick James Oct 19 '18 at 2:52

I've rid of the redundant second subselect so now it is the single one that fetch employee(s) having earliest date of birth (highest age), their IDs, names, IDs of their managers and names of managers.

SELECT ds.dept_no
     , ol.emp_no     AS oldest_emp_no
     , ol.birth_date AS oldest_bd
     , e1.first_name AS o_first_name
     , e1.last_name  AS o_last_name
     , dm.emp_no     AS req_manager_no
     , e2.first_name AS m_first_name
     , e2.last_name  AS m_last_name

  FROM departments  AS ds
  JOIN dept_emp     AS de ON de.dept_no = ds.dept_no
  JOIN employees    AS e1 ON e1.emp_no = de.emp_no
  JOIN ( SELECT MIN(birth_date) AS birth_date FROM employees ) 
         AS ol ON ol.birth_date = e1.birth_date
  JOIN dept_manager AS dm ON dm.dept_no = ds.dept_no
  JOIN employees    AS e2 ON e2.emp_no = dm.emp_no
  • Nice one - but, errr...., shouldn't that be MAX(birth_date) and not MIN? :-) – Vérace Oct 14 '18 at 16:14
  • @Vérace The higher age - the lower birth date :) If we looking for OLDEST one we need the lowest/earliest date of birth. – Kondybas Oct 14 '18 at 18:02
  • Gawd... I must have had the brain in neutral for that one! :-) – Vérace Oct 14 '18 at 19:59
  • Maybe I'm missing a couple of hemispheres? – Vérace Oct 14 '18 at 20:59

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.