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:

/*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;

Code (markup):

and the Data :

/*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);

Code (markup):

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.

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

Code (markup):

Any help will be appreciated.

Thanks

 

Couple things:

1. When running an aggregate query (using a GROUP BY clause) you need to group by every field in your SELECT clause that is not part of an aggregate function (i.e. COUNT, SUM, MAX, MIN, etc.). That means your query isn't going to run because you have too many fields in the SELECT clause that aren't in the GROUP BY clause.

2. Your using the wrong IF syntax. In a select query you don't use the END IF part. Here's a link to the one you want: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

Based on what you sated you want, this is the SQL:

SELECT COUNT(id) FROM users WHERE (user_name<>'ricky' AND is_admin<>1);

 

Hi
This seems like working to me so far.

I shall test this in my real application and come back to you if I have any further doubt.

Many thanks