將 MySQL 4 migrate 到 5 有 d SQL 唔得, 先create d tables 吧

CREATE TABLE  `a` ( `id_a` int(10) unsigned NOT NULL)
CREATE TABLE  `b` ( `id` int(10) unsigned NOT NULL)
CREATE TABLE  `c` ( `id_a` int(10) unsigned NOT NULL)

然後行

SELECT * FROM a, b JOIN c ON a.a_id = c.a_id

喺 5會出 Unknown column ‘a.a_id’ in ‘on clause’

查明之後發覺原來係 multiple table referencing mix 埋 join 攪出黎既禍。 首先喺 MySQL 5 個 JOIN 有些少唔同,

http://dev.mysql.com/doc/refman/5.0/en/join.html
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.

咁即係 SELECT * FROM a, b JOIN c ON a.a_id = c.a_id MySQL 跟據處理次序會變成 SELECT * FROM a, (b JOIN c ON a.a_id = c.a_id), 咁所以行唔到。解決方法就係將 a 同 b 既  table reference 調轉 SELECT * FROM b, a JOIN c ON a.a_id = c.a_id, 變左 SELECT * FROM b, (a JOIN c ON a.a_id = c.a_id), 咁就得了。

另一個方法就係 SELECT * FROM (a, b) JOIN c ON a.a_id = c.a_id, 用括號令到 a 同 b個 JOIN 既次序放到最高。 不過有時d SQL 水蛇春咁長, 慣左由左到右讀, 所以都係避免用逗號黎做 JOIN, 以免個次序亂晒籠。