CODEWITHSUNDEEP
sqloracle-database
Erdss4
Erdss4

Reputation: 1125

Listing a manager's name who has the oldest employee

I am trying to select a manager's first and last name who has the oldest employee, however I'm getting some issues because whenever I run this:

SELECT PERSONAL_DETAILS.FIRST_NAME, PERSONAL_DETAILS.SURNAME, MIN(STAFF.join_date) AS Join_Date
FROM STAFF

INNER JOIN PERSONAL_DETAILS
ON STAFF.PERSONAL_ID = PERSONAL_DETAILS.PERSONAL_ID

WHERE STAFF.ROLE_NAME = 'staff'
GROUP BY PERSONAL_DETAILS.FIRST_NAME, PERSONAL_DETAILS.SURNAME

Because the where clause is set to find all staff members it returns all staff members, if I were to change it to manager it then returns all managers. I understand why it does but I'm really not sure how to solve my problem here.

I want it so the result set to return a managers first and last name along with the date of their oldest employee.

I feel its not about how my tables are setup because I can't change them in my situation, its more to do with how can I write a SQL statement which will allow me to select/ match a name with the oldest date when there are multiple tables involved, because I need to join the staff table to personal_details for example to get hold of the managers first and last name but I need those names to match against the oldest date for example.

Upvotes: 0

Views: 2081

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Let's try building this up piece-wise. First - it appears you're looking for the person or persons who have been employed by the company for the longest period of time, rather than the oldest employees. I base that on the fact that you're using MIN(JOIN_DATE) to determine the employees to select, rather than e.g. MIN(BIRTH_DATE). So, OK - to find the earliest JOIN_DATE we use the MIN function, as you've already done:

SELECT MIN(JOIN_DATE)
  FROM STAFF
  WHERE ROLE_NAME = 'staff'

Great, that gets us the earliest JOIN_DATE for all "staff" people (as opposed to "manager" people). Now to find the person or persons with that JOIN_DATE we use the above as a subquery:

SELECT *
  FROM STAFF emp
  WHERE ROLE_NAME = 'staff' AND
        JOIN_DATE = (SELECT MIN(JOIN_DATE)
                       FROM STAFF
                       WHERE ROLE_NAME = 'staff')

OK, so now we've got the staff members who have been with the company the longest. Now, to find their manager (per your comment) we'd use the BRANCH_ID from STAFF to find the manager:

SELECT *
  FROM STAFF mgr
  WHERE mgr.ROLE_NAME = 'manager' AND
        mgr.BRANCH_ID IN (SELECT DISTINCT BRANCH_ID
                            FROM STAFF emp
                            WHERE ROLE_NAME = 'staff' AND
                                  JOIN_DATE = (SELECT MIN(JOIN_DATE)
                                                 FROM STAFF
                                                 WHERE ROLE_NAME = 'staff')

Now we have to get to the PERSONAL_DETAILS table to find the manager's name, so we'll join the PERSONAL_DETAILS into the above query

SELECT pd.FIRST_NAME, pd.SURNAME
  FROM STAFF mgr
  INNER JOIN PERSONAL_DETAILS pd
    ON pd.PERSONAL_ID = mgr.PERSONAL_ID
  WHERE mgr.ROLE_NAME = 'manager' AND
        mgr.BRANCH_ID IN (SELECT DISTINCT BRANCH_ID
                            FROM STAFF emp
                            WHERE ROLE_NAME = 'staff' AND
                                  JOIN_DATE = (SELECT MIN(JOIN_DATE)
                                                 FROM STAFF
                                                 WHERE ROLE_NAME = 'staff')

EDIT

Now, frankly, all those subqueries and such give me hives and gas and make my hand sore from all that indentation, so let's introduce something new: the Common Table Expression (CTE). A CTE is defined before the SELECT and is basically a little table all its own you can use like any other table in your main query:

WITH MIN_STAFF_JOIN_DATE AS (SELECT MIN(JOIN_DATE) AS MIN_JOIN_DATE
                               FROM STAFF
                               WHERE ROLE_NAME = 'staff'),
     EARLIEST_EMPLOYEES AS (SELECT s.*, d.FIRST_NAME, d.SURNAME
                              FROM STAFF s
                              INNER JOIN PERSONAL_DETAILS d
                                ON d.PERSONAL_ID = s.PERSONAL_ID
                              INNER JOIN MIN_STAFF_JOIN_DATE msjd
                                ON msjd.MIN_JOIN_DATE = s.JOIN_DATE
                              WHERE s.ROLE_NAME = 'staff'),
     MANAGERS AS (SELECT m.*, d.FIRST_NAME, d.SURNAME
                    FROM STAFF m
                    INNER JOIN PERSONAL_DETAILS d
                      ON d.PERSONAL_ID = m.PERSONAL_ID
                    WHERE m.ROLE_NAME = 'manager')
SELECT ee.FIRST_NAME AS EMPLOYEE_FIRST_NAME,
       ee.SURNAME AS EMPLOYEE_SURNAME,
       ee.JOIN_DATE,
       mgr.FIRST_NAME AS MANAGER_FIRST_NAME,
       mgr.SURNAME AS MANAGER_SURNAME
  FROM EARLIEST_EMPLOYEES ee
  INNER JOIN MANAGERS mgr
    ON mgr.BRANCH_ID = ee.BRANCH_ID

Upvotes: 2

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this

SELECT P.FIRST_NAME, P.SURNAME, X.join_date AS Join_Date
FROM PERSONAL_DETAILS P
INNER JOIN 
(SELECT STAFF.PERSONAL_ID,STAFF.JOIN_DATE FROM STAFF WHERE STAFF.ROLE_NAME = 'STAFF' AND JOIN_DATE = (SELECT MIN(JOIN_DATE) FROM STAFF WHERE STAFF.ROLE_NAME = 'STAFF')
) X On X.PERSONAL_ID = P.PERSONAL_ID

1st step : Get Min Join Date
2nd step : Get Staff details from 1st step
3rd step : Get Manager details from 2nd step

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You don't need aggregation here. Think order by and fetch first 1 row only:

SELECT pd.FIRST_NAME, pd.SURNAME, s.join_date
FROM STAFF s INNER JOIN
     PERSONAL_DETAILS pd
     ON s.PERSONAL_ID = pd.PERSONAL_ID
WHERE s.ROLE_NAME = 'staff'
ORDER BY s.join_date
FETCH FIRST 1 ROW ONLY;

(This works in Oracle 12c+. In earlier versions you need a subquery.)

Having shown this, I doubt it does what you want. I would expect the this to be the staff person's name, not their managers. You can figure that one out.

Upvotes: 0

Related Questions