Join Example: Joins Entirely within an External Data Source

In the Join Example: Joins between Data Sources topic our original table is in the .map project and the joined table is in an external data source.   The Edit - Join dialog also works when both the original table and the joined table are in an external data source, assuming that data source is not read-only and it allows modification of tables.   This topic provides an example where both the original table and the joined table are in an external data source.

 

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.

 

 

Suppose our project is as illustrated above, with both the publishers table and the titles table stored within an external data source, called database.

 

 

When we double-click open the publishers table we see it is the same table as before, but now from the title bar we can see it is stored within the database data source and not within the .map project.

 

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

 

 

In the joined table pull down menu, the dialog shows all of the tables and queries in the context (current) data source, which are only the two tables that are in the database data source.   We choose the titles table.

 

To keep the presentation simple, the Join dialog simply shows table and query names.  It is as though we have teleported into the database data source and are working there.

 

 

 Press the Add button to launch the Fields dialog.  Add three new fields to the publishers table that take data from fields in the titles query, and then we press Join.  Everything works as if we working within the local .map project, even though we might be working with two tables within a database that is resident on the other side of the planet.

 

 

The publishers table within the remote database data source has new fields added to it, and those new fields are populated taking data from the titles table within the remote database data source as well.

References to Other Data Sources are Top Down

References in the Join dialog to tables or queries in other data sources, using an aliasing query, only work from upper to lower data sources within the same data source hierarchy.

 

 

For example, if our publishers table is in the database data source and the titles table is in the .map project (a different data source) we cannot write a  query within the database data source that attempts to refer to the titles table at a higher level outside the data source, within the .map project, using constructions such as:

 

TABLE [root]::[titles];

 

That statement is shown in red to emphasize it will not work.  If there are other data sources within the database data source, we could refer to those as shown in prior examples, but we cannot reach up higher into the data source hierarchy or jump sideways into a different data source hierarchy.

 

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 the Join Example: Joins between Data Sources 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