sqlite - 在同一张表中执行两次JOIN时出现SQLite语法错误

Question

我有一个包含4个表的数据库：任务，用户，客户端和团队。
任务表有几个外键：


teamId，用于引用Team表中一行的外键
authorId，用于引用User表中一行的外键
assignedTeamMemberId，用于引用User表中一行的外键
clientId，用于引用Client表中一行的外键


查询任务时，我加入了User，Client和Team表。 User表被连接两次，就像引用authorId和assignedMemberId一样。这导致该查询（为了提高可读性，在子句之间增加了更多的空格）：

SELECT 
Task.*, 
Client.uuid AS clientUuid, 
Client.firstName AS clientFirstName, 
Client.lastName AS clientLastName, 
Client.address AS clientAddress, 
authorTable.uuid AS authorUuid, 
authorTable.firstName AS authorFirstName, 
authorTable.lastName AS authorLastName, 
assignedMemberTable.uuid AS assignedTeamMemberUuid, 
assignedMemberTable.firstName AS assignedTeamMemberFirstName, 
assignedMemberTable.lastName AS assignedTeamMemberLastName, 
Team.uuid AS assignedTeamUuid, Team.name AS assignedTeamName 

FROM Task 

JOIN 
Client ON Task.clientId=Client._id 
User as authorTable ON Task.authorId=authorTable._id 
User as assignedMemberTable ON  Task.assignedTeamMemberId=assignedMemberTable._id 
Team ON Task.assignedTeamId=Team._id;

执行时，将引发异常：

android.database.sqlite.SQLiteException: near "User": syntax error (code 1): , while compiling: SELECT Task.*, Client.uuid AS clientUuid, Client.firstName AS clientFirstName, Client.lastName AS clientLastName, Client.address AS clientAddress, authorTable.uuid AS authorUuid, authorTable.firstName AS authorFirstName, authorTable.lastName AS authorLastName, assignedMemberTable.uuid AS assignedTeamMemberUuid, assignedMemberTable.firstName AS assignedTeamMemberFirstName, assignedMemberTable.lastName AS assignedTeamMemberLastName, Team.uuid AS assignedTeamUuid, Team.name AS assignedTeamName FROM Task JOIN Client ON Task.clientId=Client._id User as authorTable ON Task.authorId=authorTable._id User as assignedMemberTable ON Task.assignedTeamMemberId=assignedMemberTable._id Team ON Task.assignedTeamId=Team._id

为什么这会引发语法错误？即使在JOIN子句中使用 User as authorTable？

Answer 1

每个联接都必须具有JOIN关键字，例如

FROM a
  JOIN b ON a.id = b.other
  JOIN c ON a.id = c.other

等等。否则，系统将不知道您要执行的操作或所需的联接类型。