A table to table join: We use the Edit - Join dialog to add the name of a book's publisher to a table that has a list of book titles.
Pending updates: The former Save update query check box in the Join dialog has been replaced with a new Setup Join and Edit Query button, which provides slightly different, but more convenient, operation. The Join Component button has been renamed Join. Illustrations and commentary in this topic will be updated soon.
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.
See the video version of this example in the Join Dialog Part 2 - Joins between Tables video.
The titles table has a list of book titles, with fields providing info on each title. One of those fields is a publisher ID field, called pub_id, which contains a code for the publisher of each book. We would like to add a new field, called publisher, to the table that provides the name of each publisher, which will be easier to understand than just a publisher ID code.
To do that we will use the publishers table, which provides a list of publishers with a pub_id code for each and the name of each publisher. Using the publishers table to add a publisher name to the titles table is a classic JOIN operation in SQL. We can do that with the Join dialog without any need to write SQL.
With the focus on the titles table, we choose Edit - Join from the main menu.
The dialog launches with the titles table loaded as the original table in the upper left box, and also as the joined table in the upper right box, in case we want to do a self-join. We first will choose the publishers table as the joined table.
The pull down menu for the joined table lists all tables and queries we can use in the data source, which in this case is the .map project. We choose the publishers table.
When we choose the publishers table, the dialog guesses that we want to use the pub_id field in the publishers table as the key field for that table. That is a good guess in this case, but if we want to use a different field we can choose it from the pull down menu, which will list all fields in the publishers table that can be used as key fields.
We choose the pub_id field to use as the key field in the titles table. We are setting up a join where data will be brought in for each record where the pub_id fields have the same value (equal) in both tables.
Next, we will add a new field to the titles table, to hold the name of the publisher for each title.
Press the Add button to add a new field.
The pull down menu shows fields in the joined table, the publishers table. We choose the pub_name field, which gives the name of the publisher.
A new field to be added to the original table appears, shown with blue preview background color. The transfer method is copy, the only method used when there is at most one record in the joined table for each record in the original table, and both the original field and the joined field are the same type. If they are different types and an automatic conversion can be done, the transfer method would be convert. The field name providing data from the joined table is also pub_name.
We would like to change the name of the field to be added to the original table to something more self-documenting, so we double-click into the field name to be added to the original table.
We change the name to publisher.
We check the Save update query box, and then we press Join Component.
The dialog immediately adds a new field, called publisher to the titles table, and it populates that field with the name of the publisher of each title, matching the values of the pub_id field in the titles table with the pub_id field in the publishers table to know what publisher to use for each title.
For example, everywhere the titles table has a record with a pub_id value of P01 the new publisher field will contain the name of the publisher, Abatis Publishers, that has a pub_id value of PO1 in the publishers table.
In addition, a new query appears in the Project pane, called titles Update. That query contains the SQL that the dialog used to alter the titles table.
Just before we pressed Join Component we checked the Save update query box, to create a new query in the project called titles Update. That query contains the SQL magic the dialog used to accomplish the join that we ordered. We can Run the update query any time we want to update the titles table to incorporate any changes.
Suppose we manually edit the titles table to add a new record for a book that is published by a publisher with a pub_id code of P04. We might not have the publishers table open, or if there are very many publishers we might not want to manually look up the name of that publishers. We would prefer that Manifold do the work for us of updating the table with the name of the publisher.
Suppose also that since we did our join operation there have been changes in the publishers table as well. In particular the publisher called Schadenfreude Press has changed its name to Ease of Use Press, based on complaints from their dealers that their original name was too hard to spell. We would like that change to be reflected, as well, in any updates to the titles table.
The update query that was saved is called titles Update. We right-click the query and choose Run.
Instantly, the titles table is updated to first, add the publisher name for the new title that was added, and second, to update the name of the publisher for ID P01 to the new name, Ease of Use Press. Easy!
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 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, if not millions, 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. People who link in data sources can quickly add a simple aliasing query, as illustrated in this topic, to use tables and queries from other data sources. 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.
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 Manifold readers to get a Fehily book and to read it, Manifold examples are often integrated with examples in the Fehily SQL books, such as the tables used in this topic.
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.
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.
Editing and Combining Data
Editable Results Tables