Performing a Join Between Tables in Different Databases

12.3.1 Problem

You want to use tables in a join, but they're not located in the same database.

12.3.2 Solution

Use database name qualifiers to tell MySQL where to find the tables.

12.3.3 Discussion

Sometimes it's necessary to perform a join on two tables that live in different databases. To do this, qualify table and column names sufficiently so that MySQL knows what you're referring to. We've been using the shirt and tie tables under the implicit understanding that both are in the cookbook database, which means that we can simply refer to the tables without specifying any database name. For example, the following query retrieves the combinations of items from the two tables:

mysql> SELECT shirt.item, tie.item FROM shirt, tie;
| item | item |
| Pinstripe | Fleur de lis |
| Tie-Dye | Fleur de lis |
| Black | Fleur de lis |
| Pinstripe | Paisley |
| Tie-Dye | Paisley |
| Black | Paisley |
| Pinstripe | Polka Dot |
| Tie-Dye | Polka Dot |
| Black | Polka Dot |

But suppose instead that shirt is in the db1 database and tie is in the db2 database. To indicate this, qualify each table name with a prefix that specifies which database it's part of. The fully qualified form of the join looks like this:

SELECT db1.shirt.item, db2.tie.item FROM db1.shirt, db2.tie;

If there is no current database, or it is neither db1 nor db2, it's necessary to use this fully qualified form. However, if the current database is db1 or db2, you can dispense with some of the qualifiers. For example, if the current database is db1, you can omit the db1 qualifiers:

SELECT shirt.item, db2.tie.item FROM shirt, db2.tie;

Conversely, if the current database is db2, no db2 qualifiers are necessary:

SELECT db1.shirt.item, tie.item FROM db1.shirt, tie;

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: