Not sure if this is possible or not. But I've two identical tables in two
different databases. Is it possible to retrieve data from the different
tables in one query ?

Thanks
Neil

__________________________________________________ _______________
MSN Hotmail is evolving – check out the new Windows Live Mail
http://ideas.live.com

> Not sure if this is possible or not. But I've two identical tables in two
> different databases. Is it possible to retrieve data from the different
> tables in one query ?

Yes, by using this notation:

select *
from mydatabase.mytable

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Neil Tompkins schrieb:
> Not sure if this is possible or not. But I've two identical tables in
> two different databases. Is it possible to retrieve data from the
> different tables in one query ?

(SELECT * FROM db1.table) UNION (SELECT * FROM db2.table)

If db2 on a other mysql server, it is possible to use the
federated storage engine of MySQL.

- --
Nils Jünemann
Database and System Administration

studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715
www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740
www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz
hJRxd5b07AxIlFP8/RBKQx0=
=G/4h
-----END PGP SIGNATURE-----

The databases are on the same server, however the login details for each
database are different.




>From: "Martijn Tonies" <m.tonies@upscene.com>
>To: <mysql@lists.mysql.com>
>Subject: Re: Query Two Databases
>Date: Thu, 25 Jan 2007 11:56:37 +0100
>
>
>
>
> > Not sure if this is possible or not. But I've two identical tables in
>two
> > different databases. Is it possible to retrieve data from the different
> > tables in one query ?
>
>Yes, by using this notation:
>
>select *
>from mydatabase.mytable
>
>Martijn Tonies
>Database Workbench - development tool for MySQL, and more!
>Upscene Productions
>http://www.upscene.com
>My thoughts:
>http://blog.upscene.com/martijn/
>Database development questions? Check the forum!
>http://www.databasedevelopmentforum.com
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=n...ns@hotmail.com
>

__________________________________________________ _______________
Find Love This New Year With match.com! http://msnuk.match.com

On Thursday 25 January 2007 11:08, Neil Tompkins wrote:
> The databases are on the same server, however the login details for each
> database are different.
>

A query executes with the credentials of the authentication used to set up the
connection. If you want to query two tables simultaneously across two
databases, the user used to run the query will need SELECT access to both
database.tables.
--
Scanned by iCritical.

> The databases are on the same server, however the login details for each
> database are different.

Hmm, I guess that the currently connected user needs to have access
to both databases, how else would it get the data?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

At the moment we are using mysql 3.23.58. It would appear I can't use UNION
in this version ?

Do I have any other options, without upgrading the database server version ?

Regards,
Neil




>From: Nils Jünemann <njuenemann@studivz.net>
>To: mysql@lists.mysql.com
>Subject: Re: Query Two Databases
>Date: Thu, 25 Jan 2007 12:01:48 +0100
>
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Neil Tompkins schrieb:
> > Not sure if this is possible or not. But I've two identical tables in
> > two different databases. Is it possible to retrieve data from the
> > different tables in one query ?
>
>(SELECT * FROM db1.table) UNION (SELECT * FROM db2.table)
>
>If db2 on a other mysql server, it is possible to use the
>federated storage engine of MySQL.
>
>- --
>Nils Jünemann
>Database and System Administration
>
>studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715
>www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740
>www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it
>-----BEGIN PGP SIGNATURE-----
>Version: GnuPG v1.4.6 (MingW32)
>Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
>iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz
>hJRxd5b07AxIlFP8/RBKQx0=
>=G/4h
>-----END PGP SIGNATURE-----
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=n...ns@hotmail.com
>

__________________________________________________ _______________
MSN Hotmail is evolving – check out the new Windows Live Mail
http://ideas.live.com