MySQL conditional statement

Question

Alright, so I have a query that looks like this:

SELECT 
    `orders`.*,
    GROUP_CONCAT(
        CONCAT(
            `menu_items`.`name`, 
            ' ($',
            FORMAT(`menu_items`.`price`,2),
            ')'
        ) SEPARATOR '<br>'
    ) as `items`,
    SUM(`menu_items`.`price`) as `additional`,
    `children`.`first_name`,
    `children`.`last_name`,
    `organizations`.`base_price`
FROM 
    `orders`, `order_items`, `menu_items`, `children`, `organizations`
WHERE 
    `order_items`.`menu_item_id` = `menu_items`.`id` AND 
    `order_items`.`order_id` = `orders`.`id` AND
    `orders`.`added_by` = {$user_id} AND
    `orders`.`date` > '{$cutoff}' AND
    `children`.`id` = `orders`.`child_id` AND
    `organizations`.`id` = `children`.`organization_id`
GROUP BY 
    `orders`.`id`

I know it's a monstrosity and that some people will die before not using explicit joins. Ignoring that, however, what I wish to do is to only use the CONCAT inside the GROUP_CONCAT if the menu_items.price is greater than 0, otherwise only return menu_items.name. I have had, however, no success trying to throw an IF in there. I've read the manual but all the ways that I've tried aren't working and I'm pretty sure I'm missing something on the whole conditional statements thing.

Answer 1

Have you tried using something like this?

CASE WHEN 'menu_items'.'price' = 0 THEN 'menu.items'.'name' ELSE CONCAT (etc) END

Replacing the CONCAT statement of course.

Answer 2

Something like this should work (but I didn't test it, sorry):

GROUP_CONCAT(
  CONCAT(
    `menu_items`.`name`, 
    IF(`menu_items`.`price` > 0,                          -- <condition>
      CONCAT(' ($', FORMAT(`menu_items`.`price`,2), ')'), -- <true-expr>
      ''                                                  -- <false-expr>
    )
  )
  SEPARATOR '<br>'
) as `items`,

The IF() function is really simple:

IF( <condition>, <true-expr>, <false-expr> )

The function has three arguments: the first is <condition>. If the condition evaluates to true, the function returns the result of <true-expr>. Else the function returns the result of <false-expr>.

Things get harder to get right when you use really long, multi-line expressions that contain parentheses and commas and so on. You just have to do it carefully. I suggest starting with more simple expressions and then build up.