It would be a lot easier to answer if you showed us the actual sql, but here goes:

SELECT JC.JOBNAME, M.NAME, A.NAME
FROM TBL_JOB_CARD JC
JOIN TBL_EMPLOYEE M ON JC.EMPLOYEE_ID = M.EMPLOYEE_ID
JOIN TBL_EMPLOYEE A ON JC.ASSISTANT_ID = A.EMPLOYEE_ID
WHERE ...

Generally, you need an alias for both the manager and assistant table (if not, you could get surprised in older versions of Firebird and newer versions would report an error), and it is a good habit to always use aliases.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of dinol.softedge
Sent: 27. mai 2008 10:07
To: firebird-support@yahoogroups.com
Subject: [firebird-support] joining 2 tables with 2 different foreign keys

Hi

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