Subject joining 2 tables with 2 different foreign keys
Author dinol.softedge

I am trying to do a join from table 1 (called tbl_job_card) to table 2
(called tbl_employee). Both tables have a field called employee_id
that links them in a one-to-many relationship. Now I have to add a
separate field called assistant_id to also link to employee_id from
tbl_employee. Therefore both employee_id and assistant_id link to
employee_id from tbl_employee. The reason for this is that an employee
can be the main emmployee or an assistant on a job. I give the
employee table an alias of tbl_assistant. Whenever I run the query,
however, it pulls up the same employee for both employee and
assistant. Is there another way to do this query? Thanks