CODEWITHSUNDEEP
mysqlsql
Gyandeep Sharma
Gyandeep Sharma

Reputation: 2327

SELECT query IF CONDITION

I have one problem with my SELECT query in my blog page.

I want comment count of each blog when comment status=1.

I am apply following query..

SELECT CONCAT(u.first_name," ",u.last_name) name, r.*,
       IF(c.status=1,COUNT(c.id)) as comment
  FROM users u 
 RIGHT JOIN resources r ON u.id = r.created_by 
  LEFT JOIN comments c ON r.id = c.resource_id
 WHERE r.type = 1 
   AND r.status=1 
 GROUP BY r.id 
 ORDER BY r.created_date DESC 
 LIMIT 0,5

but it giving SYNTAX ERROR..

 Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your 
 SQL syntax; check the manual that corresponds to your MariaDB server version for the right 
 syntax to use near ') as comment FROM users u RIGHT JOIN resources r ON u.id = r.created_by 
 LEFT JOI' at line 1

Please tell me where I am wrong.

Thanks

Upvotes: 0

Views: 80

Answers (3)

Ilker Eker
Ilker Eker

Reputation: 111

Select concat(u.first_name," ",u.last_name) name,r.*,    
case when c.status=1 then COUNT(c.id) end as comment
FROM users u RIGHT JOIN resources r ON u.id = r.created_by       
LEFT JOIN comments c ON r.id = c.resource_id    
WHERE r.type = 1     
AND r.status=1     
GROUP BY r.id     
ORDER BY r.created_date DESC    
LIMIT 0,5

https://www.w3schools.com/sql/func_mysql_case.asp

Upvotes: 2

Sagar Jajoriya
Sagar Jajoriya

Reputation: 2375

If statement contains three expressions. First, the expression, second the value returned if condition is true and third if condition is false so you are missing the third expression. Try the below code

SELECT CONCAT(u.first_name," ",u.last_name) name,r.*,IF(c.status=1,COUNT(c.id), 0) as comment
FROM users u RIGHT JOIN resources r ON u.id = r.created_by 
LEFT JOIN comments c ON r.id = c.resource_id
WHERE r.type = 1 
AND r.status=1 
GROUP BY r.id 
ORDER BY r.created_date DESC 
LIMIT 0,5

Upvotes: 3

Ketan Patil
Ketan Patil

Reputation: 1272

if function requires 3 parameters to be passed to it. IF(expression ,expr_true, expr_false) is how it should be used.

Have a look at https://www.w3resource.com/mysql/control-flow-functions/if-function.php

Upvotes: 0

Related Questions