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.

You can only do this with dynamic SQL, i.e. using a `prepare` statement. — Gordon Linoff, Jun 12 '14 at 21:29
possible duplicate of [MySQL, Check if a column exists in a table with SQL](http://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql) — ChristopheD, Jun 12 '14 at 21:30
How did you get into a situation where you don't know if a column exists? — wallyk, Jun 12 '14 at 22:13
The problem arose because I need to pull standardised reports from multiple different sources without a common schema or relationships. I can hand code each query but there are so many views being pulled from that it is becoming extremely difficult. — Linda Keating, Jun 12 '14 at 22:20
While sql syntax allows for semi colons, they aren't required. Is there any sql statements preceeding your if statement? — James McDonnell, Jun 12 '14 at 22:42
No The if statement is the first statement. I'll post the query in full with the semi colons removed. Still the same problem though. syntax error, unexpected IF — Linda Keating, Jun 12 '14 at 22:50
This can be a sign of poor design. It can also be a result of feature requests that need to be slipped in. For example we have a ASAP feature request that needs to sort one of the tables in a specific way, but only in one place. Rather than change all the code in all places, this is a case where we can check the schema of the table and then do an order by in the one place it's required. Having said that, this is no IMO a good long term solution, but it's OK as a patch until the next dot release. — Craig Jacobs, Jan 08 '15 at 19:48

score 11 · Accepted Answer · answered Jun 12 '14 at 21:51

11

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.

answered Jun 12 '14 at 21:51

James McDonnell

3,600
1
20
26

Thankyou. It was the Begin ... end that I was missing. Much appreciated. – Linda Keating Jun 12 '14 at 21:52
3

Hmm. Just tried this. But mysql complains about the IF 'syntax error, unexpected IF' – Linda Keating Jun 12 '14 at 21:55
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. – Michael - sqlbot Jun 14 '14 at 15:05
3

Your criticism may be accurate, but without explanation it is not helpful. – Craig Jacobs Jan 08 '15 at 19:51
This answer will only select both columns or none of the columns... Is there no way of selecting only the ones that exist? – Abraham Murciano Benzadon Jul 12 '17 at 23:53
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. – James McDonnell Jul 14 '17 at 00:14

score 1 · Answer 2 · answered Apr 03 '18 at 04:46

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:

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

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

2 Answers2

Linked