CODEWITHSUNDEEP
mysql
user784637
user784637

Reputation: 16152

How to write a WHERE statement based on the value of a CASE statement?

The following query returns a result set of all the new_name from the table names

SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names

I'm trying to search by new_name using the following query

SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names
    WHERE new_name LIKE '%Joh%'

However I get the error

Unknown column 'new_name' in 'where clause'

Is there a way I get this functionality to work without using a subquery?

Upvotes: 3

Views: 82

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726929

If you repeat the case in the where clause, the statement will work:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (CASE WHEN nickname = '' THEN fullname ELSE nickname END) LIKE '%Joh%'

It wouldn't be fast, because it wouldn't use indexes, but it should work.

A somewhat better approach would be as follows:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (nickname like '%Joh%') or (fullname LIKE '%Joh%')

This will return the same results, but it can use indexes on nickname and fullname if you define them, EDIT and change the second operand of LIKE to not use the leading %.

Upvotes: 5

Brad Christie
Brad Christie

Reputation: 101614

To me it makes more sense to use coalesce function, assuming nickname is either NULL or populated...

SELECT COALESCE(nickname,filename)
FROM   table
WHERE COALESCE(nickname,filename) LIKE '%JOE%'

Upvotes: 1

cctan
cctan

Reputation: 2023

SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names
    WHERE 
       (CASE
        WHEN nickname = '' THEN fullname ELSE nickname END) LIKE '%Joh%';

replace the column name with the whole case ... when ... then ... end

Upvotes: 2

Related Questions