How to UNION ALL specific tables based on a Parameter condition

Question

Say I have a global parameter param_filtered where the value can be 0 or 1, and i have already created tables main, transactions and transactions_filtered, all three tables return the same number of fields

Now i am trying to do a UNION ALL query on the tables, but i will need UNION ALL the transactions or transactions_filtered based on the parameter condition

I have tried

SELECT * FROM main
UNION ALL
CASE WHEN param_filtered > 0
THEN 
SELECT * FROM transactions_filtered
ELSE
SELECT * FROM transactions

but it did not work. Is there a way to do UNION specific tables only based on a parameter condition?

Thanks!

Answer 1

Put the condition in the WHERE clause of the union'd queries.

Its a variable equivalent to where 1=1 which always evaluates true to select all rows and where 1=2 which always evaluates false to prevent any rows from being selected

set @paramfiltered := 1

SELECT * FROM main
UNION ALL 
SELECT * FROM transactions_filtered WHERE @paramfiltered > 0
UNION ALL
SELECT * FROM transactions WHERE @paramfiltered <= 0

Here is a sql fiddle http://sqlfiddle.com/#!9/e6bba3/1