w3resource

PostgreSQL JOINS: Make a join with a table employees and itself to find the name and hire date of the employees who hired after a specific employee


5. Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.

Sample Solution:

Code:

SELECT e.first_name, e.last_name, e.hire_date 
FROM employees e 
JOIN employees davies 
ON (davies.last_name = 'Jones') 
WHERE davies.hire_date < e.hire_date;

Sample table: employees


Output:

pg_exercises=# SELECT e.first_name, e.last_name, e.hire_date
pg_exercises-# FROM employees e
pg_exercises-# JOIN employees davies
pg_exercises-# ON (davies.last_name = 'Jones')
pg_exercises-# WHERE davies.hire_date < e.hire_date;

 first_name | last_name | hire_date
------------+-----------+------------
 Alana      | Walsh     | 1987-09-21
 Kevin      | Feeney    | 1987-09-22
 Donald     | OConnell  | 1987-09-23
 Douglas    | Grant     | 1987-09-24
 Jennifer   | Whalen    | 1987-09-25
 Michael    | Hartstein | 1987-09-26
 Pat        | Fay       | 1987-09-27
 Susan      | Mavris    | 1987-09-28
 Hermann    | Baer      | 1987-09-29
 Shelley    | Higgins   | 1987-09-30
 William    | Gietz     | 1987-10-01
(11 rows)

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.
Next: Write a query to make a join with two tables employees and departments to get the department name and number of employees working in each department.

What is the difficulty level of this exercise?



Inviting useful, relevant, well-written and unique guest posts