How to create SQL to select on table that might not exist

[rasinc]

I would like to create a couple of SQL UNION SELECT statements that might be based on tables that do not exist. As users make changes to their different databases, some tables are created. I want to be able to combine information from multiple tables with similar structures (current and historical data). However, the historical tables don't always exist.

CASE WHEN SELECT * FROM T1 IS NOT NULL
	THEN SELECT * FROM T1 END
UNION ALL
CASE WHEN SELECT * FROM T2 IS NOT NULL
	THEN SELECT * FROM T2 END

I've simplified the above concepts but basically T1 will likely always exist but T2 will not. I want to combine any data in T2 with the data in T1 if it exists.

I know I can write some code to check the database schema to see if the table exists first then build the SQL based on those results (and this is my original approach) but while I was building it, I started wondering if an SQL statement can test for a table like in my sample.

I am using MySQL. Needless to say I am getting syntax errors in my test statement which is essentially what I have indicated here but with the real table names and a few fields fully indicated.

I hope this makes sense
TIA rasinc

[jmcilhinney]

Do you have control of the database? If so then this should be a non-issue because you should fix it so that the tables always exist. If you don't have control of the database then first put out a hit on the person who did because that's atrocious database design. There's absolutely no reason for the table not to exist. It should simply sit empty until required.

As for your question, I'm not sure whether MySQL uses the same syntax but in SQL Server you could incorporate an IF EXISTS statement.

[rasinc]

No control over the database and once sat on the lap of the developer (became VP later) who ran the department until he told me the definition of "normalisation", but that's another story. Tables are created for historical archiving on an annual basis, different tables per year...and yes I know the irony of it but the developers are another company who "know better than I how to develop software and manage databases". Just ask them.

I was trying to get an IF EXISTS statement to work but couldn't work out the syntax. The manual indicates some usage for a WHERE statement with EXISTS but I couldn't figure out how to adapt that to use it. I believe the original intent for this reference is to determine if the table has records, not if the table exists. So I was trying to use an IF EXISTS concept but couldn't work it out. I'll check out SQL Server syntax and see if I can adapt that.

Thanks

[szlamany]

Can you make a VIEW that is only accessing the tables that do exist at each site?

That way your queries pull from the VIEW.

And any site specific historical tables need to be in the VIEW's union statements.

[rasinc]

Thanks szlamany. I'd like to use Views but the company that makes the original product I am reporting on doesn't have a very good developer program and while I can create a View and it seems to function while logged in, for some reason when the original program logs in, the security on the views are changed so they cannot be accessed by subsidiary users other than the master. They also crash the upgrade process at times when the original program updates and alters/adds tables. The rerun the security assignments and the presence of a view crashes it. There are no plans to support this kind of thing for external developers.

The second issue is that I have no pre-existing knowledge about how long someone is using the program and therefore how many historical years exist.

When I get back to my office, I'll try IF EXISTS but I am not finding any references to it in MySQL so it may not translate. So I think I might just forget this idea, build a quick routine to check the Information_Schema for any specifically passed in table name and move on.

Thanks to both of you for the responses. Sometimes I wonder if I am missing the obvious but it doesn't look like it this time.