I have two tables, the first one is like this:
INTERNSHIP
ID| Term
---------
1 | 2015
1 | 2014
2 | 2016
2 | 2017
The second one is like this:
Term
Term| Term Description | End Date
---------------------------------
2014 | Summer 2014 | 8/12014
2014 | Summer 2014 | 8/12014
2015 | Fall 2015 | 12/1/2015
2017 | Spring 2017 | 4/1/2017
2017 | Spring 2017 | 6/1/2017
I need to find the "Term Description" for the most recent term for all the IDs. The desired result is like this:
ID| Term | Term Description
------------------
1 | 2015 | Fall 2015
2 | 2017 | Spring 2017
I did some research and came up with a solution like this:
SELECT INTERNSHIP.ID, INTERNSHIP.Term
FROM INTERNSHIP
WHERE (
SELECT MAX(INTERNSHIP.Term)
)
GROUP BY INTERNSHIP.ID
By that I got the distinct IDs with the most recent term, but I could not JOIN that with the Term Description. Can someone help me to get the desired result?
TRY THIS:
SELECT i.id, i.term, t.Term_Description
FROM INTERNSHIP i
INNER JOIN Term t ON t.Term = i.term
INNER JOIN (
SELECT MAX(t.End_Date) End_Date
FROM INTERNSHIP i
INNER JOIN Term t ON t.Term = i.term
GROUP BY i.ID) t1 ON t1.End_Date = t.End_Date