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.

oooh, the string formatting inside SQL makes me angry! Especially the <br>, no danger of MVC here! :) With that off my chest, you could always wrap the CONCATed string with a REPLACE(X, '<br> ($0.00)', '') if you can't figure anything else out. — joelhardi
– joelhardi, Commented Oct 23, 2008 at 23:25
Hahaha. Well sometimes you gotta do what you gotta do. And that does the trick. :) — Paolo Bergantino
– Paolo Bergantino, Commented Oct 23, 2008 at 23:25

Svetlozar Angelov · Accepted Answer · 2009-12-11 12:37:05Z

5

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.

edited Dec 11, 2009 at 12:37

Svetlozar Angelov

21.8k7 gold badges64 silver badges68 bronze badges

answered Oct 23, 2008 at 22:45

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2008-10-23 23:32:38Z

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.

Collectives™ on Stack Overflow

MySQL conditional statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related