multiple DB's in one query?
Is it possible to include tables from two seperate DBs (on same host) in the same JOIN query?
This would be handy in a project I'm planning, that's why I ask. I hate duplicating data...
Mark Hensler
If there is no answer on Google, then there is no question.
Mark Hensler posted this at 05:28 — 25th June 2001.
He has: 4,048 posts
Joined: Aug 2000
hmmm....
My dad just showed me how Access can link tables from another DB. But I want to do this on mySQL... I wonder if mySQL has aliases or something.
Mark Hensler
If there is no answer on Google, then there is no question.
Peter J. Boettcher posted this at 13:01 — 25th June 2001.
They have: 812 posts
Joined: Feb 2000
I really don't know enough about mySQL to help you on this one (you're the expert on this platform right?). It's possible to do this on SQL Server, so I would imagine it should be possible on mySQL.
PJ | Are we there yet?
pjboettcher.com
mairving posted this at 14:14 — 25th June 2001.
They have: 2,256 posts
Joined: Feb 2001
It seems like it would be easier to make a table out of the other database since the data is obviously related. There would be some performance concerns also using two databases.
I did find this bit of code that looks like it will do what you want.
<HTML><BODY BGCOLOR=FFFFFF><?php
echo "Connecting as mysql<BR>\n";
$connection1 = mysql_connect('localhost', 'mysql', '') or die($php_errormsg);
echo "connection1 is $connection1<BR>\n";
echo "Selecting test for mysql user<BR>\n";
mysql_select_db('test', $connection1) or @die("Error " . $php_errormsg . mysql_error());
echo "Connection as joyce<BR>\n";
$connection2 = mysql_connect('localhost', 'joyce', '') or die($php_errormsg);
echo "connection2 is $connection2<BR>\n";
echo "Selecting books for joyce user<BR>\n";
$db2 = mysql_select_db('techbizbookguide', $connection2) or die(mysql_error());
$query1 = "select foo from test";
$query2 = "select title, authorFirst, authorLast from bookinfo";
echo "Querying test<BR>\n";
$users = mysql_query($query1, $connection1) or die(mysql_error());
echo "Querying books<BR>\n";
$books = mysql_query($query2, $connection2) or die(mysql_error());
echo "Foos from test<BR>\n";
while (list($foo) = mysql_fetch_row($users)){
echo $foo, "<BR>\n";
}
echo "Books in techbizbookguide<BR>\n";
while (list($title, $authorFirst, $authorLast) = mysql_fetch_row($books)){
//Use trim in case we have a book by "Madonna" or "Prince" or...
echo $title, ' by ', trim($authorFirst . ' ' . $authorLast), "<BR>\n";
}
?>
</BODY></HTML>
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Mark Hensler posted this at 16:25 — 25th June 2001.
He has: 4,048 posts
Joined: Aug 2000
Peter, like they say: ..learn something every day...
They talk about various ways to address the columns here:
7.1.5 Database, Table, Index, Column, and Alias Names
They also mentioned it in one sentence here:
7.19 SELECT Syntax
So now, I should be able to do something like this:
SELECT db1.tbl1.col1, db2.tbl2.col2
FROM db1.tbl1
LEFT JOIN db2.tbl2 ON db1.tbl1.tbl2_id=db2.tbl2.id
WHERE db1.tbl1.id=X
Hmmmmm. Now, I wonder if you can do this with the two DBs on seperate hosts... I don't need this, just wondering.
Mark Hensler
If there is no answer on Google, then there is no question.
hotcut posted this at 16:26 — 28th June 2001.
They have: 133 posts
Joined: Sep 2000
From two hosts? That is imposible.
Will you even be able to get the data from a single database, whom is not hosted where your site is running? No way josay.
I don't have much expirience in SQL, but that I know for surtain.... if it was posible, how easy wouldn't it be to change anyones database, if you just like somehow gotten the name of the db?
~Casper
Mark Hensler posted this at 16:45 — 28th June 2001.
He has: 4,048 posts
Joined: Aug 2000
You can use a DB on a different host. It just requires the hostname, usename, and password.
hotcut posted this at 16:58 — 28th June 2001.
They have: 133 posts
Joined: Sep 2000
Really? Cool!
I might want to get back on that one later on...
Anyway, lets get back to the topic, so that I can learn more
~Casper
Mark Hensler posted this at 19:16 — 28th June 2001.
He has: 4,048 posts
Joined: Aug 2000
Another thing... most hosts only allow connections from users @localhost. Which means that they block users trying to connect from another host.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.