Table Join Question

Suppose I have three Tables - EMP (EmpID is PK), PROJECT (ProjID is PK, EmpID is FK) and ROLE (ProjID is FK, EmpID is FK and RoleDesc is char data).

To get the role description of an Employee in a Project, I would Join EMP, PROJECT and ROLE.

While Joining the tables, should I join using the Foreign keys like this:

((EMP join Project on Project.EmpID = EMP.EmpID) join ROLE on ROLE.empID
= Project.EMPID and Role.ProjectID=Project.ProjID)

Or using the primary keys from the original tables like this:

((EMP join Project on Project.EmpID = EMP.EmpID) join ROLE on ROLE.empID
= EMP.EMPID and ....)

In the second example, Role.EmpID is compared to Emp.EmpID instead of Project.EmpID.

Which is correct way of doing the join and why? Are there any optimization related issues?

TIA,
Srini

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 21 2000 - 15:32:11 CEST