Jump to content


Photo

Joining tables from two different databases


  • Please log in to reply
3 replies to this topic

#1 diskhub

diskhub
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 21 December 2005 - 10:41 AM

How do you cross databases joining?

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 21 December 2005 - 05:06 PM

first you need to have access to both database with the same username and password. assume we have table1 in db1 and table2 in db2
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] t1.* FROM db1.table1 AS t1, db2.table2 AS t2 WHERE t1.id=t2.id [!--sql2--][/div][!--sql3--]

As simple as that. You can also use JOIN syntax.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 December 2005 - 09:54 PM

The inconvenience of having to have similar login credentials to both DBs precludes to use of this technique for any DB that you haven't explicitly set up in this fashion. If it's up to you, keep the related tables in the same DB; if it's not, consider doing this in middleware or via temporary tables, otherwise you'll get caught with your pants down in the credentials ever change!
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 dbrimlow

dbrimlow
  • Members
  • PipPipPip
  • Advanced Member
  • 712 posts

Posted 30 December 2005 - 07:15 PM

[!--quoteo(post=330045:date=Dec 23 2005, 04:54 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 23 2005, 04:54 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
The inconvenience of having to have similar login credentials to both DBs precludes to use of this technique for any DB that you haven't explicitly set up in this fashion. If it's up to you, keep the related tables in the same DB; if it's not, consider doing this in middleware or via temporary tables, otherwise you'll get caught with your pants down in the credentials ever change!
[/quote]

I suppose the other important issue is making sure there is a common relational key (ID) between the 2 DB tables.
Dave

I am always amazed by no matter how much I know, I know I don't know as much as I thought I knew.  - D. Brimlow.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users