Table joins from multiple databases

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
mccommunity
Forum Commoner
Posts: 62
Joined: Mon Oct 07, 2002 8:55 am

Table joins from multiple databases

Post by mccommunity »

I am trying to do a table join from two databases and am not sure how. I have two databases x and z I have a list of buyers and what they bought in database x table purchases in z I have a detailed description of the users called users I need to write an sql statement to select all from purchases table but instead of displaying their user id (a number) I need to display the actual user name which it retrieves from the users table joined by user_id which is in both tables. I am using php. How would I do this? Thanks. :lol:
User avatar
Elmseeker
Forum Contributor
Posts: 132
Joined: Sun Dec 22, 2002 5:48 am
Location: Worcester, MA

Post by Elmseeker »

Even though I know absolutely nothing about how to solve your problem no matter what, I figure I may save some time here by letting you know that it sure would help the people who DO know if you told them what DB you're using...
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Table joins from multiple databases

Post by fractalvibes »

For clarification, are you trying to join from 2 tables within the same physical database, or two tables from different databases? Big, Big difference, as cross-database joins even on the big $$$$$$$$ databases
require extra software and setup - i.e. a federated database. If the two tables are within the same database, no problem. Otherwise database X
at site http://www.SiteA.com does not know anything about database Z at http://www.SomeOtherSite.com.....

Can you give some further details?

thanks,

Phil J.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

fractalvibes: i believe he is looking to join two tables from different databases but they both still reside on the same DB server.

if that is the case then the SQL syntax provides an easy way to do this. first perform the normal connection and databse selection that you would normally do. then in you SQL statement:

Code: Select all

SELECT * FROM currentDBtable, otherDB.table
hope that makes sense. the MySQL documentation is not clear on this syntax so people get easily confused. essentially if your are referencing a table outside of the currently selected DB then you prepend the DB name to the table (eg. otherDB.table). hope this helps.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

mydimension : Ah, yes, should be quite easy then. Things can get a little messy when joining between different machines/instances of the DB(which is why we are extra nice to our DBA! ;) He makes it easy for the developers.

Phil J.
Post Reply