CODEWITHSUNDEEP
mysqlsql
Nitin Kabra
Nitin Kabra

Reputation: 3246

MySQL execute SELECT based on condition

How to use CASE appropriately for executing SELECT query based on user input ?

On executing following query :

SELECT 
    CASE WHEN user_input_variable = 'y' THEN        
    (SELECT * FROM table_foo WHERE bar = '6f322766-0ec0-4d24-840f-c857a82a6efe')        

    ELSE
    (SELECT 0)
     END

If the user has selected 'y' then it should return records from the table, else it should return an empty result set.

I am getting error:

Operand should contain 1 column(s)

Upvotes: 1

Views: 880

Answers (2)

Karthikeyani Anandhan
Karthikeyani Anandhan

Reputation: 314

You could use the IF condition also.

IF user_input_variable = 'y'
    SELECT * FROM table_foo WHERE bar = '6f322766-0ec0-4d24-840f-c857a82a6efe')

Upvotes: 0

jarlh
jarlh

Reputation: 44786

The sub-query must return not more than 1 column. Do a UNION ALL instead.

Note that the number of columns must be the same, that's why you need to select null's in the second select.

SELECT * FROM table_foo WHERE bar = '6f322766-0ec0-4d24-840f-c857a82a6efe')
    and user_input_variable = 'y' 
union all     
SELECT 0, null, null... where user_input_variable <> 'y'

Upvotes: 4

Related Questions