Union ALL tables...possible with wildcard?

[Shrum]

I have a content manager that I wrote and I was looking into a simple solution for a feature I am thinking of...

All the tables in the database that my system uses are the same structurally so I want to UNION them all together to do *site searches*.

Is it possible to indicate *via a SQL statement* a UNION of all tables in a database without actually specifying them all individually?



=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[sleipnir214]

Nope. There is no wildcard for that operation.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BNPMike]

Surely you might just as well do a series of simple selects and build up the data in your programming language. Presumably you have PERL, PHP or ASP managing this process. I can't see you'd gain much in getting MySQL to do it.

 

[Shrum]

The issue is that I create a LOT of tables...each directory gets it's own table. Granted, yes, I thouhgt about having my content manager *build* the select statement but it seemed like there should be a way to UNION all the table together if need be...then again, the general thinking might be "Why not just use 1 table"....I just might have to do that instead.

Oh well...

Thanx for the help

=============================
Sean Shrum
Shrum Consulting
C.A.T.S.:

http://cats.shrum.net

Consulting:

http://consulting.shrum.net

Programming:

http://programming.shrum.net

R/C Soaring:

http://soaring.shrum.net

 

[BNPMike]

I suggest two reasons why there is no short-cut Union.

1) 99.999% of databases have tables which to some degree are not union compatible. Thus you would have to have some potentially complex method of specifying which ones to include.

2) You may have different selection criteria on some or all of the tables - again introducing complexity into the SQL syntax to do this.

So what I'm saying is writing it in eg PHP would be no more difficult than in any theoretical version of SQL.