vendredi 7 août 2015

SQLite syntax error when doing a JOIN twice from the same table

I have a database with 4 tables: Task, User, Client and Team. The Task table has a couple of foreign keys:

  • teamId, a foreign key for referencing a row in the Team table
  • authorId, a foreign key for referencing a row in the User table
  • assignedTeamMemberId, a foreign key for referencing a row in the User table
  • clientId, a foreign key for referencing a row in the Client table

When querying a Task, I join the User, Client and Team table. The User table is joined twice, as for the references authorId and assignedMemberId. That results in this query (added bit more whitespace between clauses for readability):

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;

When executed, an exception is thrown:

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

Why does this throw a syntax error? Even with User as authorTable in the JOIN clause?

Aucun commentaire:

Enregistrer un commentaire