Unknown column 'u.id' in 'on clause'

Question

My query show a error Unknown column 'u.id' in 'on clause'

my query

SELECT u.id,u.username ,sum(s.score) AS User_Score 
FROM
 `user` AS u ,
 `team_member` as tm
  LEFT JOIN score s ON (s.user_id=u.id and s.game_id=49 and s.team_id=27)
where tm.user_id=u.id and tm.team_id=27

Answer 1

You should rewrite your query with an proper INNER JOIN:

SELECT u.id,u.username ,sum(s.score) AS User_Score 
FROM
 `user` AS u 
INNER JOIN
 `team_member` as tm
tm.user_id=u.id
  LEFT JOIN score s ON (s.user_id=u.id and s.game_id=49 and s.team_id=27)
where  tm.team_id=27
GROUP BY u.id

It's a matter of precedence, see manual

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Because of that you can't use the table with the alias name u in the join condition of your LEFT JOIN. That's no problem anymore if you use explicit join syntax.

To get the desired result, you should GROUP BY u.id (MySQL permits the optimization to omit the u.username in the list of GROUP BY columns).

Answer 2

Your join is not correct:

SELECT u.id,u.username ,sum(s.score) AS User_Score 
FROM
 `user` AS u join  `team_member` as tm on tm.user_id=u.id

  LEFT JOIN score s ON (s.user_id=u.id and s.game_id=49 and s.team_id=27)
where  tm.team_id=27
group by u.id,u.username 

If you use join, you have to use it for all tables and can not mixed with the where statement.

Answer 3

Try without user AS u, just user u

SELECT u.id,u.username ,sum(s.score) AS User_Score 
FROM
  `user` u ,
  `team_member` tm
LEFT JOIN score s ON (s.user_id = u.id and s.game_id = 49 and s.team_id = 27)
where tm.user_id=u.id and tm.team_id=27