MySQL "Unknown Column in On Clause" [duplicate]

Question

I have the following MySQL query:

SELECT posts.id,  posts.name,  LEFT(posts.content, 400),  posts.author,  posts.date, users.display_name,
  GROUP_CONCAT(tags.tag ORDER BY tag_linking.pid ASC SEPARATOR ",") update_tags
FROM posts, tag_linking, tags
INNER JOIN `users`
ON posts.author=users.id;
WHERE tag_linking.pid = posts.id 
  AND tags.id = tag_linking.tid 
ORDER BY posts.date DESC

Which, was you can see, connects three tables etc. etc. Anyway, the problem is that it gives an error:

ERROR CODE:
SQL Error (1054): Unknown column 'posts.author' in 'on clause'

even though this simpler query used on another page works:

SELECT posts.id,  posts.name,  LEFT(posts.content, 400),  posts.author,  posts.date, users.display_name FROM `posts`
INNER JOIN `users`
ON posts.author=users.id

Does anyone have thoughts as to why this is occuring? Thanks for your help.

Equanimity

Any reason you're mixing 'lazy join' with 'explicit join' syntax? – Smatter

@MarcB Probably the same reason I just stumbled into this one--updating a query that used a lazy join and now requires a different explicit join. – Garbo

Answer 1

because your mix join syntax

From Mysql^[docs]

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

the solution is:

To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

Carrera

Answer 2

is it that semi colon you have ON posts.author=users.id;

Medicare

Answer 3

Giving an alias to your tables in from clause and surrounding them with parentheses would make it work. But mixing joins is still a bad practice.

Courtneycourtrai

Answer 4

It simply says that author does not exist in post table. either a spelling mistake??

Stroll