"The same table " LEFT JOINE ON " the same commen field " in MS-Access 2016

Question

I have something like this

Employe (Emp_ID , Emp_Name)
SecurityGuards (MainEmp_ID , AlternativeEmp_ID ). // Security guards are considered as Employees

My sql query as follows

  Select Employe.Emp_Name 
  From ( Employe
  LEFT JOIN ON SecurityGuards ON SecurityGuards.MainEmp_ID = Employe.Emp_ID)
  LEFT JOIN ON SecurityGuards ON SecurityGuards.AlternativeEmp_ID = Employe.Emp_ID

Now i want the query to show Both main and alternative Guards by names not IDs but it just gives me error saying "join expression is not supported " although removing the second LEFT Join works fine . Im using MS-Access 2016

Answer 1

You must join SecurityGuards to 2 copies of Employee to get the 2 names:

SELECT e1.Emp_Name AS MainName, e2.Emp_Name AS AlternativeName
FROM (SecurityGuards AS s 
LEFT JOIN Employee AS e1 ON e1.Emp_ID = s.MainEmp_ID)
LEFT JOIN Employee AS e2 ON e2.Emp_ID = s.AlternativeEmp_ID