Mysql – What am I doing wrong here

join;MySQL

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:

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

It seems that the version of MySQL you use does not parse this correctly when the internal joins are INNER ones. If they were all LEFT 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 a RIGHT join but you don't need parentheses:

SELECT UserRole.Name 
FROM 
            User 
          JOIN 
            UserRole 
          ON 
            User.UserRoleId = UserRole.Id
      JOIN              
        Account 
      ON 
        Account.UserId = User.Id 
  RIGHT JOIN 
    Opportunity
  ON 
    Opportunity.AccountId = Account.Id
Related Question