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;


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 

WHERE 
IF(user_name = 'ricky')
	is_admin=1
END IF;
GROUP BY city_name

Any help will be appreciated.

Thanks

I think you can use a properly formatted WHERE condition for this.
Like this:

WHERE (user_name NOT ‘ricky’ AND is_admin NOT ‘1’)

Someone may correct me, if I am wrong.

When I am running the following query:

SELECT id,
city_name,
user_name,
is_admin,
COUNT(*) AS city_count
FROM
test.users
WHERE (user_name NOT ‘ricky’ AND is_admin NOT ‘1’)
GROUP BY city_name

I am getting this error

Query : SELECT id, city_name, user_name, is_admin, COUNT(*) AS city_count FROM test.users WHERE (user_name NOT 'ricky…
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘ricky’ AND is_admin NOT ‘1’)
GROUP BY city_name’ at line 8

change this –

WHERE (user_name NOT 'ricky' AND is_admin NOT '1')

to this –

WHERE user_name <> 'ricky' AND is_admin <> 1 

Hi

Can you please tell me the difference between teh following:

  1. NOT
  2. <>
  3. !=

Thanks

NOT is a keyword that can be used in conjunction with certain conditions

<> and != are operators, identical in functionality, except that <> is standard sql while != is a non-standard mysql proprietary extension

Thanks for teh explanation

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.

This confuses me.


user_name   is_admin    count it?

<> ricky       <> 1          yes
<> ricky        = 1           no

= ricky         <> 1          no
= ricky          = 1           no 

This is what ParkinT/r937’s query does.
Is that what you want?

here is the revised one:

user_name is_admin count it?

<> ricky <> 1 yes
<> ricky = 1 yes

= ricky <> 1 no
= ricky = 1 yes

So that would become


WHERE user_name <> 'ricky' 
OR   (user_name = 'ricky' AND is_admin = 1)

I shall try this.

Many thanks for your help