How to get data from 2 databases

[pingLeeQuan]

Sorry if this issue is simple, but I was wondering if someone can help in gathering information form 2 tables in 2 different databases.

This is what I have... but the sql engine tells me that the second table cannot be found in the first database. It does not recognize the second database.

CustMast is the table in the CustLive Database
The default database is BlackBox and the table is tExternalData
---------
select ExternalDataID, CustMast.Unit_ID, CustMast.Tenant_ID from tExternalData
inner join CustLive.CustMast on (CustMast.TENANT_ID = tExternalData.Tenant_ID
and CustMast.UNIT_ID = tExternalData.Unit_ID)
where ExternalDataID = 51
-----------

 

[danielhozac]

One project shouldn't have, nor need, more than one database, which is why MySQL doesn't support it.

//Daniel

 

[JoSno]

Yes it does, you can say

SELECT ...
FROM DataBaseName.TableName
...

I am running version 4.0 and that works fine.

Jo

 

[Albion]

"One project shouldn't have, nor need, more than one database, which is why MySQL doesn't support it. "

I have a Human_Resource database that contains an up-to-date table with the names of all my employees. I have a customer contact database (similar to ACT! used by my sales department) which at times requires the entry of an employee name. I can't give the sales department access to the entire HR database, nor do I want to dick with putting all my tables in one database. So, why should HR have to keep two employee tables up to date? Sounds to me like One project may need more then one database.

-al

 

[sleipnir214]

Albion:
You don't have to give anyone access to an entire database. MySQL has the functionality of assigning permissions to databases, tables, and individual columns of tables.

With all your data in one database, it is more than possible to give your sales reptiles access to just the column of the HR table that contains names and nothing else.

Want the best answers? Ask the best questions:

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

TANSTAAFL!