CODEWITHSUNDEEP
sqldatabasems-accessinner-joinwhere-clause
Camus
Camus

Reputation: 846

Can I use INNER JOIN to replace WHERE?

I have three tables. Employees, Projects (Many-To-Many) and EmployeesProject.

I want to retrieve the name of each employee and their respective projects.

I was trying to use this query:

SELECT Employee.name, Project.name 
FROM Employee, Project
INNER JOIN EmployeeProject ON Employee.empNo = EmployeeProject.empNo
INNER JOIN EmployeeProject ON Project.projectNo = EmployeeProject.projectNo;

Access was giving me a syntax error. Then I've tried this query and it worked:

SELECT Employee.name, Project.name 
FROM Employee, Project, EmployeeProject
WHERE Employee.empNo = EmployeeProject.empNo AND EmployeeProject.projectNo = Project.projectNo;

My question is; is that ok to use WHERE to link tables or there is a better way to do it, like INNER...

Regards

Upvotes: 3

Views: 5492

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

  1. Brackets ARE required, even in Access 2010, when you have more than 2 tables involved.
  2. The order of tables in the JOIN is important. You cannot jump from Employee -> Project (unrelated) through to EmployeeProject (joining on both)

This works

SELECT Employee.name, Project.name 
FROM ((Employee
INNER JOIN EmployeeProject ON Employee.empNo = EmployeeProject.empNo)
INNER JOIN Project ON EmployeeProject.projectNo = Project.projectNo);

Note: You can drop the outer bracket between FROM and the final ;, but safer practice to always include it.

You can nest joins - and preserve the ordering of the tables (Employee, Project, EmployeeProject) if you nest them like this:

SELECT Employee.name, Project.name 
FROM Employee
INNER JOIN (Project 
            INNER JOIN EmployeeProject 
              ON EmployeeProject.projectNo = Project.projectNo)
  ON Employee.empNo = EmployeeProject.empNo;

Using the explicit JOIN syntax is much more flexible, because you can now show employees even if they don't have any projects on - just by changing to LEFT joins.

SELECT Employee.name, Project.name 
FROM ((Employee
LEFT JOIN EmployeeProject ON Employee.empNo = EmployeeProject.empNo)
LEFT JOIN Project ON EmployeeProject.projectNo = Project.projectNo);

Note: In datasheet view and in forms, the columns produced are named "Employee.name" and "Project.name", so you don't explicitly have to alias them, although it could be useful.

Upvotes: 5

Michael Berkowski
Michael Berkowski

Reputation: 270617

When using explicit JOINs, you do not comma-separate tables in the FROM clause. The correct syntax for an explicit JOIN (preferred over the older implicit syntax using the WHERE clause) is:

SELECT
  /* Probably will need to give these aliases since the're both called name */
  Employee.name AS empname,
  Project.name AS projname 
FROM 
  Employee
  /* Employee joins through EmployeeProject */
  INNER JOIN EmployeeProject ON Employee.empNo = EmployeeProject.empNo
  /* ...into Project */
  INNER JOIN Project ON Project.projectNo = EmployeeProject.projectNo;

If Access is complaining, it may require () around the join clauses (though I cannot find documentation which specifies it)

FROM 
  (Employee
  INNER JOIN EmployeeProject ON Employee.empNo = EmployeeProject.empNo)
  INNER JOIN Project ON Project.projectNo = EmployeeProject.projectNo;

Upvotes: 5

Related Questions