Skip to content

Instantly share code, notes, and snippets.

@johnhonan

johnhonan/subq_join.sql

Created December 5, 2017 21:07
Show Gist options
  • Save johnhonan/a73bf5c0e5050619a88f74f0c9cf7482 to your computer and use it in GitHub Desktop.
Save johnhonan/a73bf5c0e5050619a88f74f0c9cf7482 to your computer and use it in GitHub Desktop.
Download ZIP
Joining subqueries
Raw
subq_join.sql
SELECT DISTINCT
job_history.employee_id,
employees.first_name,
x.max_start,
y.min_start
FROM hr.job_history
JOIN hr.employees
ON job_history.employee_id = employees.employee_id
JOIN
(SELECT employee_id, max(start_date) as max_start
FROM hr.job_history
GROUP BY employee_id) x
ON job_history.employee_id = x.employee_id
JOIN (SELECT employee_id, min(start_date) as min_start
FROM hr.job_history
GROUP BY employee_id) y
ON job_history.employee_id = y.employee_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment