Jackt Jackt - 4 months ago 8
SQL Question

MySql CASE WHEN THEN needs WHERE too?

I wrote this query:

SELECT cola, colb, colc, CASE colb
WHEN '6kHcnevOJOSU' THEN 0
WHEN 'g45ujP0td6nw' THEN 1
WHEN 'v83f15lALyFs' THEN 2
END AS sor FROM mytable


The 'sor' column is returned correctly, the problem is that the query returns all the rows, not just the ones where one of the cases match colb !
How can I have it to return only the matched rows ?

Do I need to add a WHERE ? But I always saw this syntax without it. Thanks

Answer

Just add a where clause:

SELECT cola, colb, colc,
       (CASE colb WHEN '6kHcnevOJOSU' THEN 0 
                  WHEN 'g45ujP0td6nw' THEN 1
                  WHEN 'v83f15lALyFs' THEN 2
        END) as sor
FROM mytable t
WHERE colc IN (0, 1, 2);

Alternatively, you can use a having clause:

SELECT cola, colb, colc,
       (CASE colb WHEN '6kHcnevOJOSU' THEN 0 
                  WHEN 'g45ujP0td6nw' THEN 1
                  WHEN 'v83f15lALyFs' THEN 2
        END) as sor
FROM mytable t
HAVING sor IS NOT NULL;
Comments