I’m getting an error when trying to run this SQL query that joins multiple tables and groups results:
SELECT posts.*, categories.*
FROM posts, categories
JOIN post_replies ON post_replies.post_id = posts.post_id
WHERE posts.category_id = categories.category_id
AND posts.category_id = '" + categoryId + "'
AND posts.status = 'Active'
GROUP BY posts.post_id
ORDER BY MAX(post_replies.reply_date) DESC
The error message says: Unknown column ‘posts.post_id’ in ‘on clause’
I’m trying to get the latest posts from each group based on the most recent reply date. What am I doing wrong with the JOIN syntax? The tables exist and the columns are correct, but MySQL can’t seem to find the column reference. Any help would be appreciated.
MySQL’s execution order is causing this. When you mix comma-separated tables with explicit JOINs, MySQL processes the JOIN first - before it figures out how posts and categories connect through your WHERE clause. So when it hits the JOIN condition, it doesn’t know these tables are even related yet. I ran into this exact same issue last year and wasted hours debugging it. Just switch to explicit JOINs throughout the whole query. Replace that comma between posts and categories with a proper JOIN, and MySQL will handle the table relationships in the right order. Plus your query will be way easier to read and maintain later.
you’re mixing old and new join syntax - that’s what’s confusing mysql. rewrite it like this: SELECT posts.*, categories.* FROM posts JOIN categories ON posts.category_id = categories.category_id JOIN post_replies ON post_replies.post_id = posts.post_id WHERE... the comma syntax doesn’t work well with explicit joins.