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)