...

View Full Version : MySQL - IF Condition in Where clause?



phantom007
09-27-2011, 05:04 PM
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;




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.

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.




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

Old Pedant
09-27-2011, 07:25 PM
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.


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


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?

Old Pedant
09-27-2011, 07:31 PM
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.

phantom007
09-28-2011, 04:39 AM
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:

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

Old Pedant
09-28-2011, 04:52 AM
*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:

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.

phantom007
09-28-2011, 05:55 AM
Thanks so much. That worked!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum