1 Replies - 640 Views - Last Post: 14 March 2011 - 03:06 PM

#1 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Can't find this 1064 (syntax) error

Posted 14 March 2011 - 02:54 PM

I'm trying to locate a syntax error in this query, it keeps returning error number 1064:
DELETE
  FROM aow_game_primaries
  JOIN aow_game_stats ON aow_game_stats.game_id=aow_game_primaries.game_id
  JOIN aow_game_players ON aow_game_players.game_id=aow_game_primaries.game_id
  JOIN aow_game_status ON aow_game_status.game_id=aow_game_primaries.game_id
WHERE aow_game_status.status = 'R'
AND aow_game_stats.create_time<1300052875
HAVING COUNT(aow_game_players.user_id) = 1



And this is the mysql_error() that gets returned:

Quote

SQL error: 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 'JOIN aow_game_stats ON aow_game_stats.game_id=aow_game_primaries.game_id JOIN ao' at line 1


I can assure you that the table names and field names are correct, as they are populated by PHP constants that work in every other query. This one is baffling me guys, hope someone can help.

Is This A Good Question/Topic? 0
  • +

Replies To: Can't find this 1064 (syntax) error

#2 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Can't find this 1064 (syntax) error

Posted 14 March 2011 - 03:06 PM

Never mind, managed to fix it by doing a workaround:
DELETE agp.*
FROM aow_game_primaries AS agp
WHERE agp.game_id IN 
(
  SELECT aow_game_stats.game_id
  FROM aow_game_stats
    JOIN aow_game_players ON aow_game_players.game_id=aow_game_stats.game_id
    JOIN aow_game_status ON aow_game_status.game_id=aow_game_stats.game_id
  WHERE aow_game_status.status = 'R'
  AND aow_game_stats.create_time<1300052875
  GROUP BY aow_game_stats.game_id
  HAVING COUNT(aow_game_players.user_id) = 1 
)


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1