CODEWITHSUNDEEP
mysqlsqlcase
Jackt
Jackt

Reputation: 173

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

Upvotes: 3

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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;

Upvotes: 1

Drew
Drew

Reputation: 24960

Schema:

drop table if exists mytable;
create table mytable
(   id int auto_increment primary key,
    cola int not null,
    colb varchar(20) not null,
    colc int not null
);
insert mytable (cola,colb,colc) values
(9,'6kHcnevOJOSU',1),
(3,'v83f15lALyFs',1),
(9,'frog',1),
(3,'g45ujP0td6nw',1);

Option 1:

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

Option 2:

select cola,colb,colc,sor
from
(   SELECT cola, colb, colc, CASE colb
    WHEN '6kHcnevOJOSU' THEN 0
    WHEN 'g45ujP0td6nw' THEN 1
    WHEN 'v83f15lALyFs' THEN 2
    END AS sor FROM mytable
) xDerived
where sor is not null;

Results:

+------+--------------+------+------+
| cola | colb         | colc | sor  |
+------+--------------+------+------+
|    9 | 6kHcnevOJOSU |    1 |    0 |
|    3 | v83f15lALyFs |    1 |    2 |
|    3 | g45ujP0td6nw |    1 |    1 |
+------+--------------+------+------+

In Option 1, sor is not available in the where clause yet.

In Option 2, the derived table xDerived cleanses the use of sor by the outer wrapper.

Upvotes: 1

Related Questions