Checking existence in mysql query with CASE statement

Question

I am using CASE statement in mysql query and I have to check if a column exists or not in the same query.

Is it possible to do so? If yes please help.

My query as example -

SELECT CASE column1 WHEN 'status' THEN 'status' WHEN 'value' THEN (select CASE id WHEN id IS NOT NULL THEN 'status1' ELSE 'status2' END AS ScheduleStatus from table1 where condition ) END AS Status FROM table2 LEFT JOIN table1 ON ... WHERE condition ..;

In above query, when execute it I am getting the result "status2" (else part) even if he condition satisfy. If the "id" row does not exists at that time the result should be "status1". Please correct me.

try this - [How to check if column exists in SQL Server table][1] [1]: http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table — Idan Yehuda, Apr 03 '14 at 07:47
@idanuda: why do you think a SQL Server specific solution will work for MySQL? — , Apr 03 '14 at 08:06
But I've to check the column inside a nested CASE statement. — KunalC, Apr 03 '14 at 09:07

Idan Yehuda · Answer 1 · 2014-04-03T09:29:51.920

see the third answer in that page -

if not exists (select
                 column_name
           from
                 INFORMATION_SCHEMA.columns
           where
                 table_name = 'MyTable'
                 and column_name = 'MyColumn')

it should also work in mySql.

Or you can try this one -

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'dbName' 
AND TABLE_NAME = 'tableName' 
AND COLUMN_NAME = 'columnName'

If you want to chack this inside the CASE, do it with a FUNCTION that gets varchar - the column name and return '' or NULL if the column is not exists. replace the column name with the function-

CREATE FUNCTION Func_Check_Exists(columnName CHAR(20)) 
RETURNS CHAR(20)
DETERMINISTIC
BEGIN 

 RETURN ...;
END;

in you code -

SELECT CASE Func_Check_Exists('column1') WHEN 'status' THEN ...

Checking existence in mysql query with CASE statement

1 Answers1