Is is possible to create a single query that accesses two databases in MYSQL

Hi Experts-

I am hoping someone can tell me if this is possible.

I am running multiple websites for a common industry. Much of the product information is the same. I would like to have 1 database that accesses all of the product information and a separate database for each client that manages the brands they carry and the prices. Is it possible to create a single query that accesses both of these databases? The query would need to access the product information from one database and the pricing from another. I am not looking to do a union. That wouldn't work.

Thanks for your help.
danjenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Assuming both databases are on the same machine, according to this you can:

http://www.dottedidesign.com/node/14

Basically you access the tables via database_name.table_name and the columns via database_name.table_name.column_name.  Then just do your usual SQL joins.  
0
wolfgang_93Commented:
The MySQL id that you use to access tables in multiple databases must have permission to access all the databases involved. Which begs the question as to why bother setting up each customer in a separate database? Why not put all the customers in one database and the info in separate tables? Or how about a single table and have a "customer id" field to do queries on particular customers or some combination of them -- without needing to join tables and databases?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johanntagleCommented:
My guess would be they want to give each client the feeling of security i.e. they don't share databases with other clients.  I know that you can do it in such a way that you only give each client user account with access to its own table(s), but many clients still won't feel secure that way - they can say "what if somebody makes a mistake and gives another client access to my table? - that's more possible if we are on the same database vs separate"
0
danjenAuthor Commented:
Both of your answers were helpful and I was able to achieve the result I needed.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.