JOIN two tables from two different databases

Hi,

I am trying to join two different tables from two different databases running on two different servers. I can set up the two different connections, but can’t see how to get them both into the same SELECT statement.

Here is the sql I have right now:

  
 
mysql_select_db($database_connBrite, $connBrite);
$query_recBooks = "SELECT * FROM connAPEL.products, connBrite.products ";
$recBooks = mysql_query($query_recBooks, $connBrite) or die(mysql_error());


The problem is I don’t have permission on one of the servers because I can only specify one connection.

Would it be easier to place all records in a temporary table and pull from that one table?

You can’t do a query on two tables in two databases. When you send a query you’re sending it to the SQL server software to read your query and find the data in its DB and give you the result set. MySQL isn’t going to start talking to another MySQL server on another computer, nor does it have access to the database files to look at. It’s just nonsensical.

Get the tables in one database if you want to join them. Hopefully I haven’t said anything stupid here.

you can do a query on two tables in two databases

i’ve done it

but the databases have to be under one mysql server

I think I am going to transfer the tables I need to one database and do my query from there.

Thanks for your replys.

MS SQL SERVER you can query two tables from two or more databases on competely different machines.

well, of course, because sql server is a real database

<ducks />

:wink:

Heh. Is MySQL 5 going to add neat stuff like that?

Got any documentation/examples/links on how to do this?

If using SQL Server…

When referencing the remote table (i.e. the table in the other database), just use a full descriptor.

If in another database in the same instance of SQL Server:

[OtherDatabaseName].[Schema].[Tablename]

or if on another server:

[OtherServerName].[OtherDatabaseName].[Schema].[Tablename]

This may require some permission management and remote-server link setup. There are a few shortcuts you can take too (like leaving the [schema] reference blank under certain circumstances, creating views into the remote tables and using these views in your queries, etc.).

There are advanced performance techniques that require this remote strategy (i.e. federated servers/distributed views).

It is all in BOL. (SQL Server’s included documentation named “Books On Line”)

dude, nice second post

you’re on a roll…

Does all this apply to MySQL?

Probably not but maybe something similar exists. I cannot recall though…I haven’t touched mySQL for about three years now or Oracle for about four. Pretty much a SQL Server guy.