MySQL IF Statement?

Question

I have a big giant sql query where I select and format a bunch of rows for export into Excel. And I want to add one more role, pretty much if the rid (role id) EXISTS and IS 3 then set it to be Role 1 otherwise set it to be Role 2.

Mysql Query Currently

SELECT uc_orders.billing_first_name AS 'First Name', uc_orders.billing_last_name AS 'Last 
Name', users.name AS 'Username', uc_orders.billing_street1 AS 'Address', 
uc_orders.billing_city AS 'City', uc_zones.zone_name AS 'State',uc_orders.billing_postal_code
 AS 'Postal Code', ROUND(uc_orders.order_total,2) AS 'Order Total'  FROM uc_orders, users,
 uc_zones WHERE uc_orders.uid!='0' AND uc_orders.uid = users.uid AND uc_orders.billing_zone =
 uc_zones.zone_id AND uc_orders.modified BETWEEN '1271124575' AND '1274978899'

HOWEVER the user will not ALWAYS exist in the users_role database (it only exists if it is 3 or some other number, normally it will not exist). So if it does not exist, it would also return 'Role 2';

So

  IF users.uid EXISTS IN users_role.uid THEN
    IF users_role.uid EQUALS users.uid AND users_role.rid = 3
      THEN
        return 'Role 1'
      ELSE
        return 'Role 2';
  ELSE return 'Role 2';

How can I do this in MySQL? Or would PHP be the only way?

Fix your question title please. Make it describe your question. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Feb 24, 2012 at 18:30
How does it not? I want to do an IF statement in a MySql Statement? — Steven
– Steven, Commented Feb 24, 2012 at 18:32
The title lists (a) a widely-used technology and (b) an incredibly generic feature. There is nothing to make it stand out from other questions, there is nothing that identifies the manner in which this question is different from other questions, and nobody would ever be able to use it to find a solution if they had the same problem as you. You might as well have written "computers question" — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Feb 24, 2012 at 19:23

Niet the Dark Absol · Accepted Answer · 2012-02-24 18:36:24Z

You should be using JOIN for multi-table queries. Also consider formatting your query and using aliases for easier reading.

SELECT
    `a`.`billing_first_name` as `First Name`,
    `a`.`billing_last_name` as `Last Name`,
    `b`.`name` as `Username`,
    `a`.`billing_street1` as `Address`,
    `a`.`billing_city` as `City`,
    `c`.`zone_name` as `State`,
    `a`.`billing_postal_code` as `Postal Code`,
    ROUND(`a`.`order_total`,2) as `Order Total`,
    IF(`d`.`rid` IS NOT NULL AND `d`.`rid`=3,'Role 1','Role 2') as `Role`
FROM `us_orders` as `a`
JOIN `users` as `b` ON `a`.`uid`=`b`.`uid`
JOIN `uc_zones` as `c` ON `a`.`billing_zone`=`c`.`zone_id`
LEFT JOIN `users_role` as `d` on `a`.`uid`=`d`.`uid`
WHERE `a`.`modified` BETWEEN '1271124575' AND '1274978899'

The part that answers your actual question is the line with the IF and the one with the LEFT JOIN.

Surreal Dreams · Accepted Answer · 2012-02-24 18:32:26Z

1

Yes, there is an IF statement. There's also an IF() function. Here's the MySQL reference page for the IF statement:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

answered Feb 24, 2012 at 18:32

Surreal Dreams

26.4k4 gold badges49 silver badges61 bronze badges

Comments

RC. · Accepted Answer · 2012-02-24 18:45:11Z

Use a CASE statement. CASE(rid) WHEN 3 THEN 'Role 1' ELSE 'Role 2' END;

Info on the CASE statement can be found here

    -- Kolinks SQL using a CASE statement
    SELECT
        `a`.`billing_first_name` as `First Name`,
        `a`.`billing_last_name` as `Last Name`,
        `b`.`name` as `Username`,
        `a`.`billing_street1` as `Address`,
        `a`.`billing_city` as `City`,
        `c`.`zone_name` as `State`,
        `a`.`billing_postal_code` as `Postal Code`,
        ROUND(`a`.`order_total`,2) as `Order Total`,
        CASE (`d`.`rid`) WHEN 3 THEN 'Role 1' ELSE 'Role 2' END; as `Role`
    FROM `us_orders` as `a`
    JOIN `users` as `b` ON `a`.`uid`=`b`.`uid`
    JOIN `uc_zones` as `c` ON `a`.`billing_zone`=`c`.`zone_id`
    LEFT JOIN `users_role` as `d` on `a`.`uid`=`d`.`uid`
    WHERE `a`.`modified` BETWEEN '1271124575' AND '1274978899'

Collectives™ on Stack Overflow

MySQL IF Statement?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related