不支持SQL Join表达式 [英] SQL Join expression not supported

问题描述

以下是我的查询:

SELECT 
   UT.AgentName AS [Agent Name], 
   UT.TeamName AS Team, 
   Format(Now(),"Short Date") AS [As Of], 
   Sum(I.RegPointValue) AS Points
FROM 
   (SELECT 
       UU.AgentID, 
       (Nz(UU.LastName,'')+", "+Nz(UU.FirstName,'')) AS AgentName , 
       TT.TeamName 
    FROM 
       Users AS UU 
      INNER JOIN 
       Teams AS TT 
      ON UU.TeamID = TT.TeamID) AS UT 
 LEFT JOIN 
    (InfractionTypes AS I 
   INNER JOIN 
      (DateCodes AS D 
     INNER JOIN 
        AquiredInfractions AS AI 
     ON D.DateID = AI.DateID) 
   ON I.InfractionID = AI.InfractionID)
 ON UT.AgentID = AI.AgentID
WHERE (((D.DateValue)>=#4/1/2014#))
GROUP BY UT.TeamName, UT.AgentName, I.RegPointValue;

这是根据一个人收到的出勤率总结一个人将获得的所有积分.如果将LEFT JOIN更改为INNER JOIN，则查询有效，但仅返回已收到出勤违规的人员的姓名.但是我想要的是返回所有人的名字，如果他们没有得到任何奖励，他们的分数将为0.

What this does is sum up all of the points that a person would get depending upon the attendance infractions that they have recieved. If I change the LEFT JOIN to INNER JOIN the query works but only returns the names of people that have received attendance infractions. But what I would like is for it to return the names of all people and have 0 for their points if they haven't received any.

我尝试保存或执行时遇到的错误是Join expression not supported.我一直在尝试通过弄乱联接的顺序使它在最近几个小时内正常工作，但无济于事.使用MS-Access 2013.

The error that I get when I try to save or execute is Join expression not supported. I have been trying to get this to work for the last few hours by messing with the order of the joins but to no avail. Using MS-Access 2013.

推荐答案

您缺少一些别名.

每个用作表的内部查询 都必须赋予一个别名，并且在加入它们时，必须使用该别名，并且仅使用联接条件上的选定列.

Every inner query used as a table must be given an alias, and when joining them you must use that alias and only the selected columns on the join condition.

例如，向这些行添加别名:

For example, add aliases to these lines:

...
    ON D.DateID = AI.DateID) AS SOMETHING_1
  ON I.InfractionID = AI.InfractionID) AS SOMETHING_2

等

这篇关于不支持SQL Join表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！