0

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

  • This seems very basic SQL. Google outer join – edc65 Jun 28 '18 at 20:01
  • What rdbms & version? – Gerard H. Pille Jun 28 '18 at 20:22
  • Gerard, I am using dbeaver 5.1 to connect to a Progress OpenEdge database. – PenNerd Jun 28 '18 at 22:05
  • edc65, I ran into a problem with the OUTER JOIN. It seems FULL OUTER JOIN, and RIGHT OUTER JOIN are not implemented on the database. LEFT OUTER JOIN returns the same data as regular JOIN. – PenNerd Jun 28 '18 at 22:08
  • I took a template from the Progress OpenEdge man pages and was able to get emp005 to show up, but since I can't use the MAX function, it also returns all the assignments for each employeeid. – PenNerd Jun 28 '18 at 23:44
0

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;
  • Thanks Sticky. That does indeed work. It got me over my stumbling block. Once I fully grok what is going on within, I should be able to use it in my actual query. (it's a bit more complicated.) Thanks again. – PenNerd Jun 29 '18 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.