CASE in WHERE in mysql

Question

The question is as simple as the title says,But here is one logic. Here is my code

CREATE TABLE `inf_brand_images` (
`id` bigint(99) NOT NULL AUTO_INCREMENT,
`brand` varchar(255) NOT NULL,
`thumb` text NOT NULL,
`is_active` int(2) NOT NULL DEFAULT '1',
`cmp_brand` varchar(1024) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6458 DEFAULT CHARSET=latin1

Here is data in this table

ID | brand  | thumb  |is_active| cmp_brand
1  | NIKE   | a.png  | 1       | 
2  | DUNHILL| b.png  | 1       |
3  | NIKE   | c.png  | 1       | 123_NIKE
4  | NIKE   | d.png  | 1       | 789_NIKE

cmp_brand is prefixed with some their ids like 123_ and 789_ in my case. Now if i search for NIKE, so I have two parameters,one is NIKE and other is id_NIKE.where id may be 123 or 456 or any other.

What i want is

IF cmp_brand is '' then compare with brand ELSE compare brand AND cmp_brand.

Here is what i tried

SELECT thumb 
FROM inf_brand_images 
where is_active=1  AND 
CASE WHEN cmp_brand = '' THEN brand='$brandname' 
ELSE cmp_brand='$id_$brandname' END

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

Use an OR in the WHERE clause:

SELECT COUNT(id) as tot 
FROM inf_brand_images 
where is_active=1  AND 
((cmp_brand = '' AND brand='$brandname') OR (cmp_brand='$id_$brandname')

Possible duplicate:

MySQL Query WHERE Including CASE or IF?

edited May 23 '17 at 12:20

Community

1
1

answered Mar 06 '14 at 13:31

kaliatech

17,579
5
72
84

It will give me more result?...What if i want to search thumb for '789_NIKE' in my above case? – user3244721 Mar 06 '14 at 13:42
This is not working for me "SELECT thumb FROM inf_brand_images where is_active=1 AND ((cmp_brand = '' AND brand='Gallery One') OR (cmp_brand='CMP_123_Gallery One'))" – user3244721 Mar 06 '14 at 13:48
You've edited your original question, and your comments do not make sense. Your last comment does not say what is not working so there is no way I can help you further. I would recommend creating a new question clearly explaining your problem, what you have tried, and what is not working. – kaliatech Mar 06 '14 at 13:52
Here i did http://stackoverflow.com/questions/22226766/case-in-where-clause-in-mysql – user3244721 Mar 06 '14 at 14:03

score 0 · Answer 2 · answered Mar 06 '14 at 14:05

You can use an IF statement in your WHERE clause like so. http://sqlfiddle.com/#!2/6118450/2/0

SELECT thumb 
  FROM inf 
 WHERE is_active=1
   AND 'nike' = IF(cmp_brand <>'', cmp_brand,brand)

You can also use a CASE statement to compute a value to be compared to another value in your WHERE clause, like so.

SELECT thumb
  FROM inf 
 WHERE is_active=1
   AND 'nike' =  
         CASE 
         WHEN (cmp_brand <> '') 
         THEN cmp_brand
         ELSE brand
          END

CASE in WHERE in mysql

2 Answers2