CODEWITHSUNDEEP
sqliteandroid-sqlite
Leon Joosse
Leon Joosse

Reputation: 1079

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:

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?

Upvotes: 1

Views: 82

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

Every join must have the JOIN keyword, like

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

and so on. Otherwise the system doesn't know what you are trying to do or which kind of join you want.

Upvotes: 3

Related Questions