Hi,
Could someone please tell me when is wrong with this MYSQL query.
[syntax=sql]
SELECT
`problem`.`problem_id` AS `id`,
`problem`.`problem_title` AS `title`,
LEFT(`problem`.`problem_body`, 512) AS `preview`,
`problem`.`problem_user` AS `user`,
DATE_FORMAT(`problem`.`problem_date`, '%d/%m/%Y %H:%i:%s') AS `date`,
`comments`.`total_comments`,
DATE_FORMAT(`comments`.`last_comment`, '%d/%m/%Y %H:%i:%s') AS `last_comment`
FROM `problem`
LEFT JOIN (
SELECT
`problem_id`,
COUNT(`comment_id`) AS `title_comments`,
MAX(`comment_date`) AS `last_comment`
FROM `comments`
GROUP BY `problem_id`
) AS `comments`
ON `problem`.`problem_id` = `comments`.`problem_id`
ORDER BY `problem`.`problem_date` DESC
[/syntax]
When I run it I get the error: "#1054 - Unknown column 'comments.total_comments' in 'field list'"
I have renamed posts to problem just to let you know and the code is right in all that.
