CASE in WHERE CLAUSE 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.So for NIKE search i have two parameters,one is brand=NIKE and other is cmp_brand =123_NIKE (i cancatenate id with brand name automatically)

What i want is

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

Here is what i tried,both not working

SELECT thumb 
FROM inf_brand_images 
where is_active=1  AND 
CASE WHEN cmp_brand = '' THEN brand='NIKE' 
ELSE cmp_brand='123_NIKE' END

 SELECT thumb 
 FROM inf_brand_images 
 where is_active=1 AND 
((cmp_brand = '' AND brand='NIKE') OR (cmp_brand='123_NIKE'))

I would expect your second query to work correctly. That is the right way to approach it (logical groupings AND/OR, not a CASE) http://sqlfiddle.com/#!2/d176b — Michael Berkowski, Mar 06 '14 at 14:07
Do you expect a different result than `ID=1, ID=3` for this query? Based on my reading, that is the intended result. — Michael Berkowski, Mar 06 '14 at 14:09
Second query give me record number 1,3 BUT i need only 3.mean i need only c.png — user3244721, Mar 06 '14 at 14:10
Ok, so you want to _prefer_ a `cmp_brand` match over a `brand` match? — Michael Berkowski, Mar 06 '14 at 14:14
Do you want exactly one result back, or is it possible you would get more than one result back? — Michael Berkowski, Mar 06 '14 at 14:16
Watch out! sometimes rdms systems represent zero length strings as NULL values. In that case `column = ''` will not be true but `column IS NULL` will be. — O. Jones, Mar 06 '14 at 14:22
@OllieJones Tagged MySQL though, which treats `''` as an empty string, non-NULL. — Michael Berkowski, Mar 06 '14 at 14:23
I put in this warning because sqlfiddle turned the zero-length strings to nulls for me upon creating its INSERT statement. — O. Jones, Mar 06 '14 at 15:10

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You are on the right track with your second attempt, using logical AND/OR groupings instead of a CASE, but if you want to prefer the row matching cmp_brand over rows with an empty cmp_brand and expect only one result back, structure your ORDER BY to sort the non-empty cmp_brand first, and limit the overall result to 1.

SELECT thumb 
FROM inf_brand_images 
WHERE
  is_active=1 AND 
  ((cmp_brand = '' AND brand='NIKE') OR (cmp_brand='123_NIKE'))
/* non-empty cmp_brand will sort first */
ORDER BY cmp_brand <> '' DESC
/* and the end result is limited only to the first sorted row
   which will be the cmp_brand if matched, or the brand otherwise */
LIMIT 1

http://sqlfiddle.com/#!2/d176b/2

This works because the expression cmp_brand <> '' evaluates to the boolean true/false, which MySQL interprets as 1/0. A descending sort on those values forces the non-empty ones to sort fist (1 before 0).

Update after comments:

Since you do have the possibility of more than one row returned, you cannot rely on the ORDER BY. Instead, you can perform a LEFT JOIN against the same table. On one side, match cmp_brand = '' and on the other side match cmp_brand = '123_NIKE'. Importantly, return the thumb column from both sides of the join.

Wrap that in a subquery in the FROM clause, then at the top level you can use a SELECT CASE to prefer the cmp_brand if nonempty.

SELECT DISTINCT
  CASE WHEN cbcb IS NOT NULL THEN cbthumb ELSE bthumb END AS thumb
FROM (
  /* Return thumbs from both sides of the join */
  SELECT 
    b.thumb AS bthumb,
    b.cmp_brand AS bcb,
    cb.thumb AS cbthumb,
    cb.cmp_brand AS cbcb
  FROM
    inf_brand_images b
    /* join the table against itself with the matching cmp_brand in the join condition */
    LEFT JOIN inf_brand_images cb
      ON b.brand = cb.brand
      AND cb.cmp_brand = '123_NIKE'
  WHERE 
    /* The WHERE clause looks for empty cmp_brand on the left side of the join */
    b.brand = 'NIKE' AND b.cmp_brand = ''
) thumbs

Here is an example where 123_NIKE matches: http://sqlfiddle.com/#!2/dfe228/31
And an example where 124_NIKE does not match: http://sqlfiddle.com/#!2/dfe228/32

@Micheal: This is good,but if i want to get more records then it will give me wrong data...For example if i have another record, "(5, 'NIKE', 'e.png', 1, '123_NIKE')" then it will not work.Simple is if cmp_brand<>'' then i do not need to show/check for brand — user3244721, Mar 08 '14 at 06:55
My question is quite clear,If cmp_brand matched then do not need to compare brand.If cmp_brand do not matched then need to compare brand. — user3244721, Mar 08 '14 at 09:23
@user3244721 Ok, it is somewhat more complicated if you need more rows returned, but I found a working method. — Michael Berkowski, Mar 08 '14 at 13:45

CASE in WHERE CLAUSE in MYSQL

1 Answers1

Update after comments:

Linked

Related