Page 1 of 1

Relational Database Query over multiple DATABASES not tables

Posted: Sun Nov 07, 2004 7:01 am
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

Posted: Sun Nov 07, 2004 7:43 pm
by BDKR
Wouldn't the RDMBS in question need to support this?

Re: Relational Database Query over multiple DATABASES not ta

Posted: Mon Nov 08, 2004 4:00 am
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.

Posted: Mon Nov 08, 2004 5:34 am
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

Posted: Mon Nov 08, 2004 6:03 am
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)