多个 INNER JOIN SQL 访问

Multiple INNER JOIN SQL ACCESS

提问by emerjohn12

Syntax Error (missing Operator) in query expression 'tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - tbl_tax.emp_ID'.

查询表达式中的语法错误（缺少运算符）“tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id.'-

SELECT tbl_employee.emp_ID,
tbl_employee.emp_name,
tbl_gross.BasicSalary,
tbl_gross.totalOT,
tbl_netpay.totalGross,
tbl_tax.totalLate,
tbl_tax.allowance,
tbl_tax.SSS,
tbl_tax.PhilHealth,
tbl_tax.GSIS,
tbl_tax.HDMF,
tbl_netpay.totalDeduc,
tbl_netpay.emp_ti,
tbl_netpay.emp_wt,
tbl_netpay.emp_np
FROM  tbl_employee
INNER JOIN tbl_netpay ON tbl_employee.emp_id = tbl_netpay.emp_id
INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID
INNER JOIN tbl_tax ON tbl_employee.emp_id = tbl_tax.emp_ID;

I always get the error above.

我总是收到上面的错误。

回答by HansUp

Access requires parentheses in the FROMclause for queries which include more than one join. Try it this way ...

FROM对于包含多个连接的查询，Access 需要在子句中使用括号。试试这个方法...

FROM
    ((tbl_employee
    INNER JOIN tbl_netpay
    ON tbl_employee.emp_id = tbl_netpay.emp_id)
    INNER JOIN tbl_gross
    ON tbl_employee.emp_id = tbl_gross.emp_ID)
    INNER JOIN tbl_tax
    ON tbl_employee.emp_id = tbl_tax.emp_ID;

If possible, use the Access query designer to set up your joins. The designer will add parentheses as required to keep the db engine happy.

如果可能，请使用 Access 查询设计器来设置联接。设计器将根据需要添加括号以保持数据库引擎满意。

回答by michbau

Thanks HansUp for your answer, it is very helpful and it works!

感谢 HansUp 的回答，它非常有帮助并且有效！

I found three patterns working in Access, yours is the best, because it works in all cases.

我在 Access 中发现了三种模式，你的是最好的，因为它适用于所有情况。

• INNER JOIN, your variant. I will call it "closed set pattern". It is possible to join more than two tables to the same table with good performance only with this pattern.

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM
       ((class
         INNER JOIN person AS cr 
         ON class.C_P_ClassRep=cr.P_Nr
       )
       INNER JOIN person AS cr2
       ON class.C_P_ClassRep2nd=cr2.P_Nr
    )
  

  ;

• INNER JOIN "chained-set pattern"

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM person AS cr
  INNER JOIN ( class 
     INNER JOIN ( person AS cr2
     ) ON class.C_P_ClassRep2nd=cr2.P_Nr
  ) ON class.C_P_ClassRep=cr.P_Nr
  ;
  

• CROSS JOIN with WHERE

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM class, person AS cr, person AS cr2
  WHERE class.C_P_ClassRep=cr.P_Nr AND class.C_P_ClassRep2nd=cr2.P_Nr
  ;
  

• INNER JOIN，您的变体。我将其称为“封闭集模式”。只有使用这种模式才能以良好的性能将两个以上的表连接到同一个表。

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM
       ((class
         INNER JOIN person AS cr 
         ON class.C_P_ClassRep=cr.P_Nr
       )
       INNER JOIN person AS cr2
       ON class.C_P_ClassRep2nd=cr2.P_Nr
    )
  

  ;

• INNER JOIN“链式设置模式”

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM person AS cr
  INNER JOIN ( class 
     INNER JOIN ( person AS cr2
     ) ON class.C_P_ClassRep2nd=cr2.P_Nr
  ) ON class.C_P_ClassRep=cr.P_Nr
  ;
  

• 与 WHERE 交叉连接

  SELECT C_Name, cr.P_FirstName+" "+cr.P_SurName AS ClassRepresentativ, cr2.P_FirstName+" "+cr2.P_SurName AS ClassRepresentativ2nd
  FROM class, person AS cr, person AS cr2
  WHERE class.C_P_ClassRep=cr.P_Nr AND class.C_P_ClassRep2nd=cr2.P_Nr
  ;