CODEWITHSUNDEEP
mysql
Bert
Bert

Reputation: 855

MySql If Then in Select Statement

I want to use an IF THEN statement in a mysql select, but can't figure it out. When there are no comments yet, the commentcreated value should be the created value of the item itself. This is the query:

SELECT item.*, 
  count(comments.itemid) AS commentcount, 
  max(comments.created) AS commentcreated 
FROM table_items AS item 
LEFT JOIN table_comments AS comments ON (comments.itemid = item.id) 
WHERE item.published=1 
GROUP BY item.id 
ORDER BY item.created DESC

I'd figured this, but it doesn't work:

...
, IF max(comments.created)!='NULL'
THEN max(comments.created) AS commentcreated
ELSE item.created AS commentcreated
END IF;
FROM .......

What is the (best) way to do this?

Upvotes: 3

Views: 13756

Answers (3)

Vince Bowdren
Vince Bowdren

Reputation: 9218

You can't use the IF...THEN...ELSE construction inline in a single statement, as part of an expression to produce a single value - that's what the IFNULL and COALESCE functions are for, as ajreal and Redfilter have suggested.

IF...THEN...ELSE only works in a block of SQL code including a number of different statements e.g. in a stored procedure.

Upvotes: 1

ajreal
ajreal

Reputation: 47331

Can use IFNULL

IFNULL(max(comments.created), item.created) AS commentcreated

OR IF

IF(max(comments.created)='NULL', max(comments.created), item.created) 
AS commentcreated

Upvotes: 10

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171589

coalesce(max(comments.created), item.created) as commentcreated

Upvotes: 4

Related Questions