• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with this SELECT statement

#2
[eluser]RobertSF[/eluser]
Hello... I am looking at the full SELECT statement you posted, not the Codeigniter $this->db code. You posted:
Code:
SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, `1 0))`
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

But the correct SELECT (at least free of syntax errors) is this:
Code:
SELECT `default_blog`.*, `default_blog_categories`.`title` AS `category_title`,
`default_blog_categories`.`slug` AS `category_slug`, SUM(IF(comments.is_active = 1, 1, 0)),
`total_comments`, `default_users`.`username`, `default_profiles`.`display_name` FROM `default_blog`
LEFT JOIN `default_blog_categories` ON `default_blog`.`category_id` = `default_blog_categories`.`id`
LEFT JOIN `default_comments` ON `default_comments`.`entry_id` = `default_blog`.`id` LEFT JOIN
`default_profiles` ON `default_profiles`.`user_id` = `default_blog`.`author_id` LEFT JOIN `default_users` ON
`default_blog`.`author_id` = `default_users`.`id` WHERE `status` = ‘live’ AND `default_blog`.`created_on` <=
1413476587 ORDER BY `created_on` DESC LIMIT 100

What is the difference? The difference is here
Code:
SUM(IF(comments.is_active = 1, `1 0))` `total_comments`,

That part should be
Code:
SUM(IF(comments.is_active = 1, 1, 0)), `total_comments`,

I hope this helps. Smile



Messages In This Thread
Help with this SELECT statement - by El Forum - 10-16-2014, 11:38 AM
Help with this SELECT statement - by El Forum - 10-16-2014, 10:04 PM
Help with this SELECT statement - by El Forum - 10-16-2014, 11:24 PM
Help with this SELECT statement - by El Forum - 10-17-2014, 10:17 AM
Help with this SELECT statement - by El Forum - 10-17-2014, 11:21 AM
Help with this SELECT statement - by El Forum - 10-17-2014, 01:31 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.