SELECT from Multi* table

Databases
1

Folks,

How would I do a select that searches multiple tables, but instead of listing all tables I want to do something like: WHERE TABLE_NAME LIKE ‘a%’

I basically want something like this:

SELECT email FROM TABLE_NAME LIKE ‘a%’ WHERE email =‘foo@doo.com’;

Thanks

2

you can’t do it that way, you’ll have to search each table separately (maybe do them all in a big UNION query)

why do you have multiple tables for emails?

3

As far as I know, you can use matching patterns if you use the shell of myslq but I have never tried to do a select query where I needed to use a matching pattern.

I do know that in Access and SQL Server you can do something like

[COLOR=#00008b]select[/COLOR] * [COLOR=#00008b]from[/COLOR] sys.tables [COLOR=#00008b]where[/COLOR] name [COLOR=#00008b]like[/COLOR] [COLOR=#800000]'%table%'[/COLOR]

so if you’re using any of these, then the answer will be yes.

On any other database, I would say that there has to be a way but right now I wouldn’t be able to know how.

4

Thanks Rudy. I was battling with it for a while, but yeah UNION sprung into my mind too. Why multiple tables for email? Well that’s the one million dollar question, I inherited it all.

5

MySQL allows something similar:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘a%’;

But further querying the result set of all tables in the above query is the tricky or not straight forward part without having to do as r937 suggested.

6

in most databases, this is the INFORMATION_SCHEMA

Edit: darn, sniped by five minutes… i’m getting slow

it would require two queries, one for the metadata, and then one or more data queries (one if you use the UNION approach)

SQL Server and MySQL both support INFORMATION_SCHEMA, which i believe is part of the SQL standard

7

I thought that there had to be something similar… I’m glad that I was right :slight_smile: Thanks.

I didn’t suggest the union approach because I assumed that ma201dq wanted to query for tables with similar name first.