CODEWITHSUNDEEP
sqlms-access
kazzi
kazzi

Reputation: 534

How to join same table twice on Access

I know on MySQL we can join the same table twice by giving each table its own alias, but that doesn't seem to be working on Access.

For example:

SELECT d.departmentID, d.depName, d.location, c1.memberID, c1.fullName,  c1.reportsTo, c2.fullName
FROM Departments as d
INNER JOIN Contacts as c1
   ON c1.departmentID = d.departmentID
INNER JOIN Contacts as c2
   ON c1.reprtsTo = c2.memberID

Doing that gives me a syntax error. Does anyone know how I can join the same table (Contacts) to get the name of the person the member reports to (c2.fullName)?

Update, the Error I'm getting:

Syntax error (missing operator) in query expression 'c1.departmentID = d.departmentID INNER JOIN Contacts as c2 ON c1.reportsTo = c2.memberI'.

Upvotes: 1

Views: 3082

Answers (3)

Hopper
Hopper

Reputation: 146

just drag the table in to the visual editor twice. It automatically renames the second instance of the table as "_1". 

SELECT Contacts.EmpID, Contacts_1.EmpID AS reportsTo
FROM Contacts INNER JOIN Contacts AS Contacts_1 ON Contacts.SupervisorID= 
Contacts_1.EmpID;

Upvotes: 1

forpas
forpas

Reputation: 164194

In such cases it's easier to let the Access design editor take care of the joins and the aliases.
The code below is based on your code but created by the design editor:

SELECT 
  Departments.departmentID, Departments.depName, Departments.location, 
  Contacts.memberID, Contacts.fullName, Contacts_1.reportsTo, Contacts_1.fullName
FROM (
  Departments INNER JOIN Contacts ON Departments.departmentID = Contacts.departmentID
) INNER JOIN Contacts AS Contacts_1 ON Contacts.reportsTo = Contacts_1.memberID;

Upvotes: 1

Parfait
Parfait

Reputation: 107767

In MS Access, more than one JOIN requires parentheses pairings:

SELECT d.departmentID, d.depName, d.location, c1.memberID, 
       c1.fullName,  c1.reportsTo, c2.fullName
FROM (Contacts as c1
INNER JOIN Departments as d
   ON c1.departmentID = d.departmentID)
INNER JOIN Contacts as c2
   ON c1.reprtsTo = c2.memberID

Upvotes: 5

Related Questions