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;
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);
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;
Any help will be appreciated.