Table joins from multiple databases
Moderator: General Moderators
-
mccommunity
- Forum Commoner
- Posts: 62
- Joined: Mon Oct 07, 2002 8:55 am
Table joins from multiple databases
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.
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
Table joins from multiple databases
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.
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.
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
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:
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.
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-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas