Join Example: Joins between Data Sources

The Edit - Join dialog works with tables and queries within the same data source.   This topic shows how to use tables and queries from other data sources by aliasing them into the current data source.   Queries can be used as the joined table, but not as the target table.

 

To follow along with table examples in this topic, download from the Examples page on the Manifold website the books.mxb project.  That project contains the publishers and titles tables used in examples.

 

Consider the project shown below:

 

 

Four tables, including our example publishers table, are in the .map project data source.   They are stored in the project and not in some external data source.   One table, our example titles table, is in an external data source, called database, that is linked into the project.   That external data source could be any of hundreds of different data sources that can be linked into a project.  It could be a linked Manifold .map project in a folder on our computer, or it could be an Oracle database hosted by a server on the other side of the world.

 

 

We can open the publishers table to see it is the same table as in prior examples.

 

 

The titles table is also the same table as in prior examples, but it now is stored in a different data source.  We can see from the title bar it exists within the database data source.   To make this table accessible to the Join dialog for use with the publishers table, we have to create a small query that takes data from the table in the database data source and makes it available within the same data source, that is, the .map project.   That is easy to do.

 

 

We right-click into the .map portion of the project (that is, outside of the database data source) and choose Create - New Query.

 

 

We specify a name of titles for the new query and press the Create Query button.

 

 

That creates a new query in the project called titles.   The name does not conflict with the table called titles in the database data source, because the full name of the table in the data source is [database]::[titles] and not just titles.   See the Example: Refer to a Table within a Data Source  topic for more information on referring to tables within data sources.

 

We double-click open the new titles query and add the following bit of SQL:

 

TABLE [database]::[titles];

 

Running the above query generates a results table that is exactly the same as the titles table within the database data source, but because the query exists in the main .map project part of the project it is in the same data source (the .map project) as the publishers table.

 

The query uses the TABLE statement to repeat data from a table in a different data source, specifically, the [database]::[titles] table.  It is like creating a Windows shortcut in File Explorer.   

 

The new titles query will now appear in the Join dialog.

 

 

With the focus on the open publishers table window, we choose Edit - Join in the main menu.

 

 

In the joined table pull down menu, we now see the titles query.   We choose the titles query as the joined table.

 

 

Press the Add button to launch the Fields dialog.  We add three new fields to the publishers table that take data from fields in the titles query.  Press Join.

 

 

As before, the Join dialog takes data from the joined table to populate the new fields within the publishers table.  In this case, the data is coming from a table within an external data source, and not from a table stored inside the .map project.

 

Notes

Terminology - The original table also may be called the target table, and the table providing data also may be called the source table.

 

Quick reference - With the Join dialog open, press F1 to open a quick reference guide in a browser window.

 

Guessing the key field - When we launch the dialog with an original field and when we choose a joined field, the dialog will try to guess what fields we might want to use as key fields and will load those first by default.  If it guesses wrong, we can choose the field we want.   For the original table, the dialog tries to use a field (other than mfd_id) that has a BTREE / BTREENULL index, with a data type preference for numbers, then text, then everything else, and a name preference for field names with id in them, such as ... id or ..._id, then ...id and then everything else. For the joined table, the dialog uses similar logic, but first tries to use a field with the same name as in the original table.

 

Added fields are static - Added fields are static, that is, if data in the joined table changes the fields in the original table that received data in the Join operation from the joined table will not automatically be updated.  We can easily update the table any time we want using a saved update query.  

 

Spatial joins are automatically parallel - Joining data between drawings uses parallel GeomOverlay function variants.  Joins from images to drawings are parallelized through a nested SELECT using THREADS.

 

Why setup, and then update? -  Why does the Setup Join and Edit Query button first add the new field desire and then generate an UPDATE query, instead of generating a query that both adds the new field and also does the UPDATE?  Two steps are used to enable repetitive use of the same query to adjust what data is joined into the new field.   If the generated query also created that new field, to run that query multiple times, each time we ran it we would first have to delete the newly created field from the schema, so that part of the query would not cause an error.   By generating only an UPDATE query we can modify it and re-run it however many times we want, to try out different variations of how we UPDATE the table.

 

Why only in the same data source? - The Join dialog works only with tables and queries within the same data source to keep the dialog simple and fast.   An important part of keeping the dialog simple is to show only those tables or queries in the pull down list for the joined table that can be used in a join.   Scanning all tables and queries in the same data source is reasonable, but if there are many data sources or many levels of nesting (data sources within data sources) there could be hundreds of thousands of tables and queries within the hierarchy to scan.  For people working in map projects without such elaborate data sources, most of the time what they do will be within the .map project data source so everything works by default.  To use tables and queries from other data sources as the joined table in the Join dialog we can quickly add a simple aliasing query, as illustrated in this topic.  This method also makes it possible for the same Join dialog to work within other data sources, when aliasing queries refer back either to the .map project data source or to other data sources.   Almost all queries can be used as the source, joined table.  The Join dialog also can also use a query as the target, original table, to be used to copy data into the table through the query, as shown in the Join Example: Join into a Query and Join Example:  Join into a Query into a Selection topics, if the result table is writable and the Join does not attempt to create new columns in the table.

 

Buy and read a Fehily book on SQL - Manifold recommends Chris Fehily's excellent books on SQL.  Chris makes learning SQL really easy.  To encourage users to buy a Fehily book and to read it, Manifold examples often use examples similar to those in the Fehily SQL books.

 

Example data - To follow along with table examples in this topic, download from the Examples page on the Manifold website the books.mxb project.  That project contains the publishers and titles tables used in examples.

 

Videos

Join Videos

 

Find Percentages of Open Space in ZIP Code Area - Find the percentage of open space in each ZIP code area given a layer of polygons representing ZIP codes and a layer of polygons showing open spaces like parks and green spaces. This video shows how to do that start to finish in a few simple steps, from initial importing of shape files to final results, in just five minutes, with an additional few minutes of explanation what each step does. Works in Manifold Release 9 or using the free Manifold Viewer.

See Also

Maps

 

Tables

 

Queries

 

Drawings

 

Images

 

Editing and Combining Data

 

Join

 

Join Videos

 

Join Examples

 

Command Window

 

JOIN Statements

 

Editable Results Tables