MySQL execute SELECT based on condition

Question

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)

The sub-query must return not more than 1 column. Do a UNION ALL instead of using a case expression. — jarlh
– jarlh, Commented Apr 16, 2019 at 13:25
Please explain the logic behind your query. In terms of pseudo code, it appears that you would always just be executing the SELECT * FROM table_foo. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 16, 2019 at 13:27
What's the parameter? What's the expected output when the parameter has one value and the other value(s)? — The Impaler
– The Impaler, Commented Apr 16, 2019 at 13:28
The second query SELECT 0 returns a different number of columns than the fist one. — The Impaler
– The Impaler, Commented Apr 16, 2019 at 13:29
@TimBiegeleisen : I have updated the question. If the user has selected 'y' then it should return records from the table, else it should return an empty result set. — Nitin Kabra
– Nitin Kabra, Commented Apr 16, 2019 at 13:35

jarlh · Accepted Answer · 2019-04-16 13:38:37Z

4

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'

edited Apr 16, 2019 at 13:38

answered Apr 16, 2019 at 13:37

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tim Biegeleisen Over a year ago

Beat me to it +1, but I think you've been sitting on this longer than I have :-)

jarlh Over a year ago

@TimBiegeleisen, I started writing this minutes ago, but had to do some other stuff before finishing it.

Nitin Kabra Over a year ago

I have been using this query (MySQL 5.6.2) for a while, it suddenly stopped working (MySQL 8.0), CASE WHEN '{3}' = 'y' THEN SELECT * FROM transaction_bank_allocation WHERE company_code = '{0}' AND r_trans_id = {1}; ELSE SELECT 3 AS no_bank_alloc_selected; END CASE;

Nitin Kabra Over a year ago

Also, there are multiple when cases in real situation, where there are joins based on the user input

Karthikeyani Anandhan · Accepted Answer · 2019-04-16 13:54:57Z

0

You could use the IF condition also.

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

answered Apr 16, 2019 at 13:54

Karthikeyani Anandhan

3341 silver badge6 bronze badges

Collectives™ on Stack Overflow

MySQL execute SELECT based on condition

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related