Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Question MySQL - IF Condition in Where clause?

    Hi

    I was wondering if we can use SQL's IF ELSE condition in the where clause?

    Let me explain you what exactly I am looking for:

    Consider the following DDL:

    Code:
    /*Table structure for table `users` */
    CREATE TABLE `users` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `city_name` varchar(50) DEFAULT NULL,
      `user_name` varchar(50) DEFAULT NULL,
      `is_admin` tinyint(1) DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

    and the Data :
    Code:
    /*Data for the table `users` */
    
    insert  into `users`(`id`,`city_name`,`user_name`,`is_admin`) values (1,'AA','joe',1),(2,'BB','smith',0),(3,'CC','ricky',0),(4,'DD','mathew',0),(5,'EE','ricky',1),(6,'FF','martin',0),(7,'AA','parry',0),(8,'AA','james',0),(9,'BB','ricky',1);

    I want to do a count of the cities available in the table but at the same time I want to put a condition such that:

    The system should count ALL cities available, but in case if the user_name is ricky, the system should also check if the is_admin column is 1. If either of these conditions fail, the count for this row should NOT happen.

    So in our case, row # 3 should NOT be counted as the is_admin for it is set to 0.





    I am trying to run the following query but it seems like MySQL does not support IF ELSE in the where clause.

    Code:
    SELECT 	id, 
    	city_name, 
    	user_name, 
    	is_admin,
    	COUNT(*) AS city_count
    	FROM 
    	test.users 
    
    WHERE 
    IF(user_name = 'ricky')
    	is_admin=1
    END IF;
    GROUP BY city_name


    Any help will be appreciated.

    Thanks
    Last edited by phantom007; 09-27-2011 at 05:24 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    MySQL only supports IF...ELSE in stored procedures.

    For ordinary queries, you can use the IF(...) function or the CASE WHEN construction.

    HOWEVER...

    You don't need anything like that for this simple condition.
    Code:
    SELECT id, city_name, user_name, is_admin,	COUNT(*) AS city_count
    FROM  test.users 
    WHERE NOT ( user_name = 'ricky' AND is_admin = 0 )
    GROUP BY id, city_name, user_name, is_admin
    or, 100% equivalent
    Code:
    SELECT id, city_name, user_name, is_admin,	COUNT(*) AS city_count
    FROM  test.users 
    WHERE user_name != 'ricky' OR is_admin != 0
    GROUP BY id, city_name, user_name, is_admin
    However...

    However you will be surprised by the results of that.

    Maybe you need to tell us what results you are trying to get?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Here's a rule for you:

    When you use GROUP BY, be sure that you include *EVERY FIELD NAME* in the SELECT that is not part of an aggregate function.

    The aggregate functions are those such as COUNT(), SUM(), AVG(), MIN(), MAX(), etc.

    ALL other databases enforce that rule and will give you an error if you forget a field name.

    MySQL is sloppy and allows you to omit field names. *BUT* then the result is *NOT* what you might think it is and you will often misinterpret the results you are getting.

    Until and unless you *THOROUGHLY* understand what MySQL does when you omit fields from the GROUP BY, you should follow the rule.

    In 5 years of using MySQL, I have only found a reason to violate the rule ONE TIME. And even then I could have followed the rule and gotten my answer a different way.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    hi Sir

    I should get the count for the "AA" city as 3 as there are 3 rows for the "AA" city. but in your sql I get 1 AA for each row.

    Screenshot below:


  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    *EXACTLY* what I was trying to tell you!

    Read what I wrote:
    However you will be surprised by the results of that.
    Maybe you need to tell us what results you are trying to get?
    *NOW* try this:
    Code:
    SELECT city_name, COUNT(*) AS city_count
    FROM  test.users 
    WHERE NOT ( user_name = 'ricky' AND is_admin = 0 )
    GROUP BY city_name
    Time to go read up on GROUP BY and what it means.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Thanks so much. That worked!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •