SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Why does this generate a syntax error in Mysql 4.x?

    Code:
    SELECT * FROM (SELECT * FROM `tasks` as `task` WHERE `task`.`department_id` = `6` AND `task`.`parent_id` = `0` AND `task`.`progress` <> `100` ORDER BY `task`.`modified` DESC LIMIT 5) as `Task` LEFT JOIN `members` AS `AssignedTo` ON `Task`.`member_id` = `AssignedTo`.`id` LEFT JOIN `members` AS `AssignedBy` ON `Task`.`assigned_by_id` = `AssignedBy`.`id` LEFT JOIN `departments` AS `Department` ON `Task`.`department_id` = `Department`.`id` ORDER BY Task.priority asc
    Thanks for your help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please post your queries with some minimum formatting

    it doesn't have to be as pretty as mine, just don't have it all on one line, as that makes it next to impossible to read
    Code:
    SELECT * 
      FROM (
           SELECT * 
             FROM `tasks` as `task` 
            WHERE `task`.`department_id` = `6` 
              AND `task`.`parent_id` = `0` 
              AND `task`.`progress` <> `100` 
           ORDER 
               BY `task`.`modified` DESC LIMIT 5
           ) as `Task` 
    LEFT 
      JOIN `members` AS `AssignedTo` 
        ON `Task`.`member_id` = `AssignedTo`.`id` 
    LEFT 
      JOIN `members` AS `AssignedBy` 
        ON `Task`.`assigned_by_id` = `AssignedBy`.`id` 
    LEFT 
      JOIN `departments` AS `Department` 
        ON `Task`.`department_id` = `Department`.`id` 
    ORDER 
        BY Task.priority asc
    i have a couple of ideas as to what might be wrong, but instead of guessing, i'm just gonna ask you... what was the error?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay sorry about the formatting.

    Well it simply was a syntax error:

    "1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM `tasks` as `task` WHERE `task`.`department_id"

    :/

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    knowing exactly where the syntax error occurs will often tell you what the problem is

    in this case, the problem is that your version of mysql is so old, you cannot even use subqueries

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2003
    Location
    Berlin, Germany
    Posts
    1,829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Okay, thanks for the help.


Bookmarks

Posting Permissions

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