Join Example: Join into a Query

The source, joined table used with the Edit - Join dialog can be a query, but 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, if the result table is writable and the Join does not attempt to create new columns in the table.

 

This allows joining data to, for example, a table alias, such as TABLE [datasource]::[table]  or to a selection in an existing component, such as  TABLE CALL Selection([component], true) .

 

This works with queries that have been saved as a query component in the project pane, and not to queries that exist only within a Command Window, which have no persistent name and, as such, cannot be addressed from an update query.

 

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 4 - Joins into Queries  video.  

Example

We will create a query and then copy data from the publishers table into the query using the Join dialog.

 

 

We use the same publishers table as modified in other examples.

 

 

The titles table we will use has had a publishers field added to it.   The publishers field is full of NULLs, which we will populate with publisher names from the publishers table, using the pub_id as a key field that is common to both tables.

 

In the Project pane, we right-click and choose Create - New Query.   We create a new query called titles query.

 

 

We double-click the titles query query to open it in a Command Window, and then we enter the SQL query

 

SELECT [title_id], [title_name], [pub_id], [publisher] FROM [titles];

 

When we enter the SQL text it is automatically stored in the query.   We run the query by pressing the ! Run button in the main toolbar to get the results table shown above.   The results table is shown with white background, which means the fields we have chosen are sufficient for the results table to be writable, that is, we have picked fields providing necessary indexes for it to be writable, in this case, the title_id field the underlying table uses as an index.

 

With the focus on the open titles query window, we choose Edit - Join.

 

 

The Join dialog launches with the titles query automatically loaded as the original table.   We choose the pub_id field for the key field.    

 

We choose the publishers table as the joined table, with the pub_id field automatically being loaded as the key field.   

 

The Add button is not enabled, because we cannot add new fields to a query: the only fields available to us are those in the SELECT list of the query.  We can only write to fields that are exposed as writable in the results table of the query.  That includes the publishers field we want to update with joined data.  

 

We double-click into the joined table cell for the publishers field.

 

 

From the pull down list of fields from the publishers table, we choose the pub_name field.  Press Join.

 

 

The publishers field, formerly full of NULLs, is now updated with values joined from the publishers table, the join working through the query to update the table.

 

If we want to see what SQL query Manifold used to do the update, with the focus on the titles query window, we can launch the Edit - Join dialog again.

 

 

We set it up as before, and then click the Setup Join and Edit Query button.

 

 

That launches the Command Window with the query used, an UPDATE query.    The text of the query is:

 

-- $manifold$

--

-- Auto-generated

-- Join

--

 

UPDATE (

  SELECT

    t.[title_id] AS tkey0,

    t.[publisher] AS t0,

    s.[pub_name] AS s0

  FROM [titles query] AS t LEFT JOIN [publishers] AS s ON t.[pub_id] = s.[pub_id]

) SET

  t0 = s0;

 

The text shows a compact example of how to do a join update of a table by writing through a query.  

 

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 this topic and in the  Join Example:  Join into a Query into a Selection topic, 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 - 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

See the Join Videos list of videos showing how to use the Join dialog.

See Also

Maps

 

Tables

 

Queries

 

Drawings

 

Images

 

Editing and Combining Data

 

Join

 

Join Videos

 

Join Examples

 

Command Window

 

JOIN Statements

 

Editable Results Tables