MySQL Select column if it exists otherwise select another column

Question

I have multiple databases with the same architecture, same table and column names. With exception, one column name differs among the databases even though the values are the same. I need to write a universal MySQL query which can be applied to all the databases. I need to overcome that one problem where the column name may differ among the databases.

I have tried to lookup the problem in Google and the previous stackoverflow topics, none of them answered to my problem. I tried to solve the problem with 'case when ... then... else' and 'if exists...' statements - doesn't work or I am doing something wrong.

select(case when (select count(*) > 0
    from information_schema.COLUMNS
    where table_name = 'myTable'
    and column_name = 'EUR')
then EUR
else USD
end)
from myTable

I expect to have a working query which checks if the 'EUR' column exists, if it does then select it, and, if it doesn't, then select 'USD' instead.

Did you have a look at this https://stackoverflow.com/questions/24194223/mysql-how-to-only-select-from-a-column-if-the-column-exists? — Sorix, Aug 28 '19 at 09:09
You need to use Dynamic SQL using Prepared statements. Check this [answer-1](https://stackoverflow.com/a/53108294/2469308), [answer-2](https://stackoverflow.com/a/53144473/2469308), [answer-3](https://stackoverflow.com/a/53147936/2469308) and [so on](https://stackoverflow.com/search?q=Dynamic+SQL+user%3A2469308)... — Madhur Bhaiya, Aug 28 '19 at 09:09
Sorix, I check that https://stackoverflow.com/questions/24194223/mysql-how-to-only-select-from-a-column-if-the-column-exists didn't work. Madhur, thanks, I will take a look on that. — LordRudolf, Aug 28 '19 at 09:16
Is it too late(or impractical) to suggest you have a db master table which holds the currency the db is using and change the column names to something universal. — P.Salmon, Aug 28 '19 at 09:31
P.Salmon, unfortunately, far too late. Otherwise, that column name would be changed to "currency" in the first place, instead of having 2 different names distributed among many databases. — LordRudolf, Aug 28 '19 at 09:55

score 0 · Answer 1 · answered Aug 29 '19 at 16:12

You can do this as either of:

Use two queries: the first to figure out what column you want to use, then from your software compose a new SQL statement with the correct column to use.
Use server-side Prepared SQL Statements to compose your query from the first query on the server side. See also https://stackoverflow.com/a/986088/53538 as an example.

I'm not sure what you use to run your queries, but if it is any comfortable programing language, there is basically no advantage to approach 2 - its not faster unless your MySQL connection has really large latencies - and it is likely more complicated to use.

MySQL Select column if it exists otherwise select another column

1 Answers1