Relational Database Query over multiple DATABASES not tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Relational Database Query over multiple DATABASES not tables

Post by robster »

Hi all,

I keep reading loads of ideas on how to do a query based on an ID or similar over multiple tables but for the life of me can't figure out how to do a query based on an ID match on table contents over multiple databases.

I'm using PHP if anyone has any example code or tutorials I'd really appreciate it :)

Rob
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Wouldn't the RDMBS in question need to support this?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Relational Database Query over multiple DATABASES not ta

Post by Weirdan »

robster wrote: I keep reading loads of ideas on how to do a query based on an ID or similar over multiple tables but for the life of me can't figure out how to do a query based on an ID match on table contents over multiple databases.
You didn't state the db server in use. For example, MySQL is able to perform queries across databases(schemata) if those databases are located on the same db server.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

I beg your pardon. I am using MySQL. Both the databases are on the one server.

Would you (or anyone) be able to point me to an example of how to do this? I work best on example rather than theory. I really appreciate this :)

just FYO, What I am trying to do is create my own simple image gallery. I have a forum I am using for user login etc. I have it setup so if user #3 uploads an image, it records what user uploaded the image in the appropriate table (user 3 in this case). What I am doing now is saying WHO user 3 is. This is why I need to cross reference tables over multiple databases.

Thanks again :)

Rob
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

robster wrote: Would you (or anyone) be able to point me to an example of how to do this? I work best on example rather than theory. I really appreciate this :)
Here it is:

Code: Select all

mysql> create database test;
Query OK, 1 row affected (0.06 sec)

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table a(b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values(1);
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table c(d int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into c values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.a inner join test2.c on test.a.b=test2.c.d;
+------+------+
| b    | d    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (7.16 sec)

mysql>
As you can see, I created two tables in two different databases, then I performed cross-database query using full table/column names (test.a.b, test2.c.d)
Post Reply