Joining two tables with unrelated where clauses

join;union

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,

Bill

Best Answer

LEFT JOIN one subquery to get the maximum of FTE per employee and another to get the maximum (or minimum, ...) assignment for each employee and FTE.

SELECT e1.*,
       a4.*
       FROM employee e1
       LEFT JOIN (SELECT a1.employeeid,
                         max(a1.ftepercentage) ftepercentage
                         FROM empassingments a1
                         GROUP BY (a1.employeeid)) a2
                 ON a2.employeeid = e1.employeeid
       LEFT JOIN (SELECT a3.employeeid,
                         a3.ftepercentage,
                         max(a3.assignment) assignment
                         FROM empassignments a3
                         GROUP BY a3.employeeid,
                                  a3.ftepercentage) a4
                 ON a4.employeeid = e1.employeeid
                    AND a4.ftepercentage = a2.ftepercentage
       WHERE e1.enabled = 1;
Related Question