Re: searching multiple tables and databases

 

>I'm currently looking for the best way to search across multiple tables and databases.

 

You use a union query to join the results from multiple queries from different tables. For example:

 

Employees Table

1    Peter

2    Paul

3    Mary

 

Customers Table

1    John

2    Ringo

3    Paul

 

SELECT 'Employee' AS what, id, name

FROM Employees

WHERE name ~* '^P'

UNION

SELECT 'Customer' AS what, id, name

FROM Customers

WHERE name ~* '^P'

ORDER BY name;

 

Would return something like:

Employee    2    Paul

Customer    3    Paul

Employee    1    Peter

 

I can't tell you how you would do something like this accross multiple databases. There may be better ways...