Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Exclamation Unanswered: Query on MYSQL 5

    Hello the next query used to work on mysql 4 but It doesn't on mysql 5, could you please tell me why?

    Pd. I recive this error: ERROR 1054: Unknown column 'mantis_bug_table.id' in 'on clause'


    SELECT DISTINCT mantis_bug_table.*, UNIX_TIMESTAMP(last_updated) as last_updated
    FROM mantis_bug_table, mantis_project_table, mantis_bug_text_table LEFT JOIN mantis_bugnote_table ON mantis_bugnote_table.bug_id = mantis_bug_table.id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_text_table.id = mantis_bugnote_table.bugnote_text_id

    WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ((mantis_bug_table.project_id='73') OR (mantis_bug_table.project_id='86') OR (mantis_bug_table.project_id='42') OR (mantis_bug_table.project_id='33') OR (mantis_bug_table.project_id='2') OR (mantis_bug_table.project_id='59') OR (mantis_bug_table.project_id='27') OR (mantis_bug_table.project_id='90')) AND (mantis_bug_table.status<>'90') AND ((summary LIKE '%datos%') OR (mantis_bug_text_table.description LIKE '%datos%') OR (mantis_bug_text_table.steps_to_reproduce LIKE '%datos%') OR (mantis_bug_text_table.additional_information LIKE '%datos%') OR (mantis_bug_table.id LIKE '%datos%') OR (mantis_bugnote_text_table.note LIKE '%datos%')) AND (mantis_bug_text_table.id = mantis_bug_table.bug_text_id)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem occurs here --

    Code:
    FROM mantis_bug_table
        , mantis_project_table
        , mantis_bug_text_table LEFT JOIN mantis_bugnote_table 
              ON mantis_bugnote_table.bug_id = mantis_bug_table.id 
    the portion in bold is evaluated on its own, and of course in that context, the reference to mantis_bug_table is invalid

    see http://bugs.mysql.com/bug.php?id=13551

    solution: rewrite your queries using JOIN syntax only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •