6 Replies - 8353 Views - Last Post: 28 September 2011 - 04:15 AM

#1 cancer10  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 52
  • Joined: 12-July 08

MySQL - IF Condition in Where clause?

Posted 27 September 2011 - 09:05 AM

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.

Posted Image



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.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL - IF Condition in Where clause?

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,854
  • Joined: 16-October 07

Re: MySQL - IF Condition in Where clause?

Posted 27 September 2011 - 09:47 AM

Your GROUP BY doesn't make any sense there. Nor does the IF. A WHERE is an IF for the SELECT.

The CASE statement is essentially an IF for the result of the SELECT, which I think it what you're looking for.

Perhaps something like:
SELECT id, city_name, user_name, 
	(case when user_name = 'ricky' then 1 else is_admin end) as is_admin
FROM  test.users 


Was This Post Helpful? 1
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: MySQL - IF Condition in Where clause?

Posted 28 September 2011 - 03:27 AM

View Postcancer10, on 27 September 2011 - 04:05 PM, said:

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.

If I am understanding you correctly, you want everybody counted except "ricky" users who aren't admins?

Or to put it differently:
WHERE 
    user_name != 'ricky'
OR  is_admin = 1



Also, make sure the GROUP BY clause comes after the WHERE clause. Otherwise it won't run.
Was This Post Helpful? 0
  • +
  • -

#4 cancer10  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 52
  • Joined: 12-July 08

Re: MySQL - IF Condition in Where clause?

Posted 28 September 2011 - 03:34 AM

View PostAtli, on 28 September 2011 - 03:27 AM, said:

View Postcancer10, on 27 September 2011 - 04:05 PM, said:

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.

If I am understanding you correctly, you want everybody counted except "ricky" users who aren't admins?

Or to put it differently:
WHERE 
    user_name != 'ricky'
OR  is_admin = 1



Also, make sure the GROUP BY clause comes after the WHERE clause. Otherwise it won't run.



Thanks for your help.

I shall try this.
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: MySQL - IF Condition in Where clause?

Posted 28 September 2011 - 03:34 AM

View Postbaavgai, on 27 September 2011 - 04:47 PM, said:

Perhaps something like:
SELECT id, city_name, user_name, 
	(case when user_name = 'ricky' then 1 else is_admin end) as is_admin
FROM  test.users 


In cases where there is only one "case", you could also use the IF() function to simplify it a bit:
SELECT
    id, city_name, user_name,
    IF(user_name='ricky', 1, is_admin) AS is_admin
FROM  test.users 


Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,854
  • Joined: 16-October 07

Re: MySQL - IF Condition in Where clause?

Posted 28 September 2011 - 03:58 AM

Perhaps. Or set you up for future failure.

The CASE statement is SQL standard and will work in most SQL engines. In Oracle you can use DECODE like that MySQL IF and have same problem.

I'd recommend avoiding proprietary solutions if a more standard one is available.
Was This Post Helpful? 1
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: MySQL - IF Condition in Where clause?

Posted 28 September 2011 - 04:15 AM

Good point. I guess I wasn't thinking much outside the MySQL bubble there :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1