Working with multiple tables in a Query

Often Queries will use information from a single source, however it is possible to specify multiple sources.

• Joins – where relationships are defined between tables, allowing you to add columns from each. See below... 
• Unions – where tables are combined to form a resultset. Read more...

Joining tables

A Join (known technically as an 'Inner Join') is used to indicate a relationship between two tables, and tells the Query to combine records from each table based on matches at the join.

To use fields from more than one table

1. Right-click in the top pane and choose Add Source
2. Select and Add each of the sources
3. Close the Add Source dialog
4. Drag the fields down from the list in the top pane to the grid below
5. Join the tables in the top pane (see below)

To specify a join

1. Click on the source field in one table, and
2. Drag to the corresponding field on the other table
3. A line will be displayed to indicate the join

To remove a join

1. Click on the join line to select it. When selected it is shown in bold
2. Right click on the line to display its context menu
3. Click 'Remove Join'

It is possible (albeit unusual) to join two tables with more than one join line. In that case each join relationship must match simultaneously for the records to show in the resultset.

It is possible for more than two tables to be involved in joins, with 'table a' joining to 'table b' and 'table b' joining to 'table c' etc.

Cross joins

If more than one table is added to a Query, but there is no join line, then the resultset will show each row in the first table multiple times, once for each row in the second table. This is known as a 'Cross Join', or 'Cartesian Product Join'.

This can be useful if the second table only has one record, to provide access to the field values in that table.