Rob Rudloff
asked on
SQL how to join a MAX on a SELECT sub-query ?
Hi. Forgive the title I gave this question ... I'm not sure how to ask it except through this example ---
I have a table of employees EMP:
Any suggestions?
Thanks!
I have a table of employees EMP:
EmpID LastName Status
------|----------|-------
39 | Carter | A
40 | Reagan | I
41 | Bush | A
42 | Clinton | A
44 | Obama | A
and a table of their dates of attendance ATTEND:
id EmpID Attended
---|------|-----------
1 | 44 | 08/01/2016
2 | 42 | 06/01/2016
3 | 41 | 06/01/2016
4 | 40 | 04/01/2016
5 | 39 | 04/01/2016
6 | 39 | 03/01/2016
I need to create a SELECT that shows which active status "A" employees have not attended since 05/01/2016 (mm/dd/yyyy). The result, I imagine, should look like this:
EmpID LastName lastdate
------|----------|-----------
39 | Carter | 04/01/2016
I've tried several ways of doing this with joined "select" statements, but none seem to work right.Any suggestions?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks YZlat! Seems simple now that you have shown me. (I was making it way too complicated, and I had the syntax of my JOIN wrong.)
Glad I could help
Open in new window