CODEWITHSUNDEEP
mysqlwordpressjoin
Soroush Falahati
Soroush Falahati

Reputation: 2327

MYSQL - Inner Join Syntax Error

Yeah, little confused here. I have number of tables in one of my wordpress installations and the name of them and the field names are pretty much descriptive. So I don't think that you need any explanation for them. My problem here is actually with the forth inner join I am trying to do. The query is simple:

SELECT * 
FROM wp_posts AS post 
    INNER JOIN wp_postmeta AS postmeta ON post.ID = postmeta.post_id 
    INNER JOIN wp_game AS game ON post.ID = game.post_id
    INNER JOIN wp_game_platform AS platform ON game.game_id = platform.GameId
    INNER JOIN wp_platform_release AS release ON release.PlatformID = platform.ID
WHERE post.post_type = 'games' AND postmeta.meta_key = 'post_views_count' AND platform.Status = 0 AND release.ReleaseDate > 1435917562
ORDER BY postmeta.meta_value DESC, game.game_id
LIMIT 100

This is supposed to get list of all the game platforms (Xbox version, PC version of a game) that are not released yet and sort them with their page visits. As you can see the page visits are stored as post meta so I did a join on wp_postmeta. The above query gives me this syntax error:

failed : 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 'release ON release.PlatformID = platform.ID WHERE post.post_type = 'games' AND ' at line 1

Which clearly indicated that there is a syntax error in the query that I can't see. Funny enough if I run the following query, it returns the expected results (all the games [released or not] sorted by their post visits):

SELECT * 
FROM wp_posts AS post 
    INNER JOIN wp_postmeta AS postmeta ON post.ID = postmeta.post_id 
    INNER JOIN wp_game AS game ON post.ID = game.post_id
    INNER JOIN wp_game_platform AS platform ON game.game_id = platform.GameId
WHERE post.post_type = 'games' AND postmeta.meta_key = 'post_views_count' AND platform.Status = 0
ORDER BY postmeta.meta_value DESC, game.game_id
LIMIT 100

At first I thought that there is a JOIN limit but after research I found out that the limit is more than 60 tables which is far from 5. So you see any thing that I can't?

Thanks

Upvotes: 0

Views: 59

Answers (1)

PaulF
PaulF

Reputation: 6773

release is a reserved word - try changing the name of the alias to something else. See : https://dev.mysql.com/doc/refman/5.0/en/keywords.html

Upvotes: 2

Related Questions