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
dBforums
>
MySQL - IF Condition in Where clause?
09-27-11, 12:06
MySQL - IF Condition in Where clause?
Linear Mode
