mysql error: Unknown column 'thread.threadid' in 'on clause'

Question

I'm getting the following error...

ERROR:
mysql error: Unknown column 'thread.threadid' in 'on clause'

I hear it has something to do with the MySQL version... what changes do I need to make to my PHP code to get passed this error? I read that This error arises because the comma operator was used in the SELECT query. This query was not designed to work under the new MySQL 5 strict query parser, which treats commas as lower precedence than joins.

Database error in vBulletin 3.0.7:

Invalid SQL: 
    SELECT thread.threadid, thread.forumid
    FROM thread AS thread, subscribethread AS subscribethread
    LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
    WHERE subscribethread.threadid = thread.threadid
    AND subscribethread.userid = 1
    AND thread.visible = 1
    AND lastpost > 1277054898
    AND deletionlog.primaryid IS NULL

mysql error: Unknown column 'thread.threadid' in 'on clause'

mysql error number: 1054

Can I simply take out the comma in the SELECT thread.threadid, thread.forumid?

Please explain...

Answer 1

You want to join thread with deletionlog but the query is trying to join subscribethread with deletionlog. Replace it with:

FROM subscribethread AS subscribethread, thread AS thread
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')