SQL Question

I need some help with an SQL statement. I have two tables. Emp_Users and Emp_UserJob. Both of these tables are connected by a UserID. Each time an employee changes their employment status, they receive a new UserJobID number. Also, they have several different leadership levels. What I am trying to do is view the latest leadership levels that are equal to leadershiplevel 20. What I need to do is select the MAX UserJobId and then select all of the employees that have a leadership level of 20. This is what I have right now:

SELECT DISTINCT U.UserID, U.FirstName, U.LastName, U.MiddleName, U.UserID, J.LeadershipLevel, U.EmploymentStatus, J. UserJobID
FROM Emp_Users As U INNER JOIN Emp_UserJob As J on (U.UserID=J.UserID)
WHERE J.UserJobID = (SELECT MAX(UserJobID)
FROM Emp_UserJob) AND J.LeadershipLevel = 20

I believe that all this will show me is one employee with the highest UserJobID with the LeadershipLevel of 20 when I need to see all employees' highest UserJobID with the leadershiplevel of 20. I appreciate any help.

Post a sampling of your database data and a sample of what you are expecting the query to return and I will try to help you.

Here is a sample on employees information: Sorry about the formatting.


UserJobID - LeadershipLevel
667 - 0
2139 - 20
2140 - 20
2141 - 20
2364 - 0
2365 - 0
2366 - 20


In this case, I want the statement to look at the max UserJobID (2366) and then compare it to the LeadershipLevel that corresponds with it. (20) If the Highest UserJobID has a LeadershipLevel of 20, then I want to see their information like First, Last, Middle names, etc. The SQL statement should ignore the other userJobID's since I want it to compare the MAX UserJobID. At first I was just selecting all records that in which LeadershipLevel = 20, but there may be an instance when previous UserJobId's = 20 but the MAX UserJobID would = 30. It would still show me that employees record since there was a 20 in the LeadershipLevel somewhere along the way. I'm basically trying to weed out the most current records that = 20.

LastName FirstName MiddleName LeadershipLevel

Doe John M 20

I meant sample data for both tables.

Have you tried.

Will this grab the highest or most recent UserJobID? I guess that I was assuming that I would need to use the MAX function in the statement.

EmpUsers
UserID - LastName - FirstName - MiddleName
1 John
2 Mary
3 Tom
4 David
5 Mike


EmpUserJob
UserID - UserJobID - LeadershipLevel
1 - 667 - 0
1 - 2139 - 20
1 - 2140 - 20
1 - 2141 - 20
1 - 2364 - 0
1 - 2365 - 0
1 - 2366 - 20

Does This help any?