I am trying to fetch User's Role Name by traversing through multiple tables in MySql. If I pull User.Id everything works but right after I add one more level for UserRole.Name it throws an error.
Error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Opportunity.AccountId = Account.Id' at line 2
DB Tables
Opportunity has AccountId as column.
Account has UserId as column.
User has UserRoleId as column.
UserRole has Name as column.
Query
SELECT UserRole.Name FROM Opportunity
LEFT JOIN Account JOIN User JOIN UserRole ON User.UserRoleId = UserRole.Id ON Account.UserId = User.Id ON Opportunity.AccountId = Account.Id LIMIT 1\G
Best Answer
The syntax is valid SQL. I'm formatting a bit differently and adding parentheses to show how it is / should be parsed:
It seems that the version of MySQL you use does not parse this correctly when the internal joins are
INNER
ones. If they were allLEFT
joins, it wouldn't have a problem.There are 2 solutions:
A) Use the above, with the parentheses.
B) Rewrite, changing the order of the joins, so the they not nested. You'll have to replace the
LEFT
to aRIGHT
join but you don't need parentheses: