Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: 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.

    http://i.imgur.com/Y9VXG.png



    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 cancer10; 09-27-11 at 13:25.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't boither with an if statement, even if MySQL supported such a syntax

    its all down to your where clause
    If I read your post right
    then you want all rows except where the userid = ricky and is_admin = true

    Code:
    where not (userid='ricky' and isadmin = false)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2008
    Posts
    36
    Quote Originally Posted by healdem View Post
    don't boither with an if statement, even if MySQL supported such a syntax

    its all down to your where clause
    If I read your post right
    then you want all rows except where the userid = ricky and is_admin = true

    Code:
    where not (userid='ricky' and isadmin = false)
    A small alteration in your understanding.

    I want:

    all rows except where the userid = ricky and is_admin = 0

Posting Permissions

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