Hallo,
I'd like to join 2 db in one:
I'd like to do query dealing the 2 db.

I'd like to Join these db only for some querues and I want that these 2 db
are separated and continue to be separated.

For example I have DbA and DbB.
in DbA there is table: cities (name,state)
in DbB there is table: person (name, city)

I'd like, for example, query:
SELECT DbB.person.name , DbA.cities.state FROM
DbA.cities.name=DbB.person.city


How can I do?
Thank You in avance.
Mario.

mario.lat_ wrote:
> I'd like, for example, query:
> SELECT DbB.person.name , DbA.cities.state FROM
> DbA.cities.name=DbB.person.city

Yes, MySQL supports database specifiers in the FROM clause, but the
syntax is different from what you use above.

Here is what I would write:

SELECT B.person_name, A.state
FROM DbA.cities AS A JOIN DbB.person AS B
ON A.name = B.city

Regards,
Bill K.

Thank you for answering me.
But if 2 database are on different machine?
It is possible to something like that? how?
Thank you for your help.
Mario.

> mario.lat_ wrote:
>> I'd like, for example, query:
>> SELECT DbB.person.name , DbA.cities.state FROM
>> DbA.cities.name=DbB.person.city
>
> Yes, MySQL supports database specifiers in the FROM clause, but the
> syntax is different from what you use above.
>
> Here is what I would write:
>
> SELECT B.person_name, A.state
> FROM DbA.cities AS A JOIN DbB.person AS B
> ON A.name = B.city
>
> Regards,
> Bill K.

mario.lat_ wrote:
> Thank you for answering me.
> But if 2 database are on different machine?
> It is possible to something like that?

No, a given MySQL instance can access only databases that are on the
same host.

Regards,
Bill K.

mario.lat_ wrote:
> But if 2 database are on different machine?
> It is possible to something like that? how?
> Thank you for your help.

Take a look at the federated storage engine:

http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

It was implemented to allow references to tables at other MySQL instances.

Greetings
Kai

--
This signature is left as an exercise for the reader.

"mario.lat_" <mario.lat@libero.it> wrote:

> But if 2 database are on different machine?
> It is possible to something like that? how?

Beginning with version 5.0.3 MySQL supports the FEDERATED table engine:

http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

Using it you can access remote MySQL tables as if it was a local table.
FEDERATED tables still have a lot of limitations and are not
particularly fast. Maybe it is good enough for your needs.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/