Mysql How to only select from a column if the column exists

Question

I need to be able to check if a column exists and if it does then I want to SELECT from it.

I am trying lots of different variations but I'm not even sure if this is possible.

Here's my latest attempt:

SELECT
IF (EXISTS (SELECT `Period` AS `Period` FROM myview), `PERIOD`,
IF (EXISTS (SELECT `Country` AS `COUNTRY` FROM myview),`COUNTRY` FROM myview ;

Any ideas?

---

EDIT

---

I had seen the other question on here: MySQL, Check if a column exists in a table with SQL

But I still struggle with the if statement. I can check to see if the column exists using the answer in the question above. But my question is - how to execute a select statement from that column if the result is found to be true.

---

EDIT 2

---

The answer below indicates that I should use the BEGIN and END statement and this makes sense. However, my query complains at the first line. It says 'unexpected IF' - can anybody confirm if this is the right syntax fro MYSQL?

if( exists (SELECT * 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'view_name' 
    AND COLUMN_NAME = 'column_name') )
begin
    SELECT `column_name` FROM `view_name`
end

Thanks in advance.

Solution 1

This query will give you whether a column exists.

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'

If you want to check if some columns exist then perform a select statement you need to first check your columns exist. Then perform the select:

if (exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'myview' AND COLUMN_NAME = 'Period') and exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'myview' AND COLUMN_NAME = 'Country'))
begin
    select `Period`, `Country` from myview
end

If the IF condition is true, then you will execute anything inside the BEGIN and END.

Solution 2

I came across the same situation where I had some product tables created by sheets uploaded by users. Sometimes, the sheets did not have column named "obsolete", so I had to import all products from the sheet but not the obsolete ones.

I am not modifying my query based on the original question that was asked, but here is my solution:

SELECT
    t2.model,
    (
        SELECT
            COUNT(*) AS _count
        FROM db.table t1
        WHERE
            `obsolete`=1
            AND t1.model=t2.model
    ) AS `obsolete`
FROM (
    SELECT
        0 AS `obsolete`,
        t3.model
    FROM db.table t3
) t2

There are 2 most important parts in this query:

1. We are selecting 0 AS obsolete as dummy to fool MySql which will be used even if column does not exist when selecting COUNT(*).
2. We have named tables as t1 & t2 to match the column model as t1.model=t2.model.

Comments

[Linda Keating]

Thankyou. It was the Begin ... end that I was missing. Much appreciated.

[Linda Keating]

Hmm. Just tried this. But mysql complains about the IF 'syntax error, unexpected IF'

[Michael - sqlbot]

This only thing accurate about this answer is the reference to the information_schema.columns table. The rest of it is sending OP down a path that has little chance of actually providing a workable solution.

[Craig Jacobs]

Your criticism may be accurate, but without explanation it is not helpful.

[Abraham Murciano Benzadon]

This answer will only select both columns or none of the columns... Is there no way of selecting only the ones that exist?

[James McDonnell]

Actually I take that back, that won't work because mysql will check that the column exists when running the query before it would return any data. What is the case that your table may or may not have a column? Generally you probably shouldn't have this sort of problem.