I have two tables: (EMPAssignments) and (EMPloyees). Both have an employee id column.
EMPAssignments has columns for employeeid, assignment, and ftepercentage:
employeeid assignment ftepercentage emp001 sales 0.5 emp001 shipping 0.3 emp001 marketing 0.2 emp002 sales 0.2 emp002 shipping 0.7 emp002 marketing 0.1 emp003 sales 0.5 emp003 shipping 0.5 emp003 marketing 0.5
EMPloyees has columns for employee id and enabled
employeeid enabled emp001 1 emp002 1 emp003 1 emp004 0 emp005 1
I want my query to return the assignment with the highest FTEpercentage or a blank assignment if they do not exist in the EMPAssignments table. I should have rows for employees 1,2,3,and 5, not 4.
In the case of a tie, I don't care which assignment is returned as long as it is only one, but there should always be a row for every active Employeeid.
I really am not much with SQL but I have searched about and tried various things.
I can use this to get the highest ftepercentage for each employeeid in the EMPAssignments table.
SELECT "employeeid", MAX("ftepercentage") AS top_assignment from EMPAssignments group BY "employeeid"
And I can use:
SELECT Employeeid FROM EMPloyees WHERE enabled !=0
to return the enabled employees.
But what I can't figure out is how to join the two because emp004 and emp005 don't exist in EMPAssignments. My join attempts either return all the assignments for all the active employeeids, or the top ftepercentage for only those employeeids in the EMPAssignments table.
Can anyone point me in the right direction?
Thanks in advance,