query not compatible with MySQL 5?

dodozhang21

Hi, I have a query that uses outer join. it was working fine with older mysql version but gives syntax error in mysql 5? Do you have any idea what's wrong with it?

SELECT m.username, m2.username as mod, t.* FROM transactions t LEFT JOIN members m ON (m.uid=t.moneyfromuid) LEFT JOIN members m2 ON (m2.uid = t.mod_id) WHERE t.moneytouid = '1' ORDER BY t.timestamp DESC

thanks

stolzyboy

i don't have the ability to test it at the moment, but you may have a read on this page

http://dev.mysql.com/doc/refman/5.0/en/join.html

it lists some changes to mysql5 conforming more closely to the sql standard, so that may impact your join methods

stolzyboy

dodozhang21

thanks. i skimmed through them and none of the examples seem to talk about joining the same table twice?

Lars_Berg

Mod is a reserved word.

stolzyboy

Lars Berg wrote:
Mod is a reserved word.

as it was in mysql4.1, so if his query worked in mysql4.1 and not below... i dunno why it wouldn't now, unless they got more strict on that, but if you WERE using <4.1 that's likely your issue

maybe the query never worked?

stolzyboy

dodozhang21

this query has worked before of course. i've been using the application for years.
and it seems it is due to "mod" being a newly reserved word in mysql. after i changed it to "moderator" - everything worked. thank you!