Semaphor

Joining Tables on a Card

Start a card from a database table and join other tables to it, one join at a time

A card starts from one table. To use columns from another table, join it to the card. Each joined table appears in the field list as a folder, so you can see which table every field comes from.

Using a semantic domain?

Cards built on a semantic domain dataset reach related datasets through the domain's relationships, with no joins to set up. See the semantic domain user guide.

Pick the Card's Table

Choose the table from the dataset dropdown at the top of the editor. The dropdown lists the tables already used on this dashboard; choose Browse all data and the Direct scope to browse a connection's databases, schemas and tables.

To change the table later, pick another one. Columns with the same name and type stay on the card, and the card's joins are removed with the old table. A message lists what changed, with Undo.

Join a Table

  1. At the bottom of the field list, choose Join a table to … (it stays there as you scroll).
  2. Pick the table to join. The dialog suggests the columns to match on (for example orders.customer_id = customers.id); change them if you need to.
  3. Choose which rows to keep, then Join.

The joined table appears as a folder where the column you matched on was. Open it to use its columns.

To join a table to a table you already joined, open that table's folder menu and choose Join a table to ….

The Join Dialog

SectionWhat you set
Match rows whereOne or more conditions: a column of each table and an operator (=, <, >, <=, >=). With several conditions, choose whether all or any must be true. Add another set adds a set that must also hold.
RowsKeep every row of the table you joined from, or Only rows with a match. More options adds keeping every row of the joined table, or every row from both.
Matches per rowHow many rows of the joined table one row can match. Not set until you set it.
SQLThe join as SQL, read only.

Joins that repeat rows

When one row matches several rows of the joined table (an order and its line items), the row repeats once per match, so sums of the first table's columns grow. Set Matches per row to say so; the dialog then shows which sums repeat.

Edit or Remove a Join

Open the joined table's folder menu:

  • Edit join opens the dialog with the join's settings.
  • Remove join removes the join, any join from that table, and the fields that read them. A message lists what was removed, with Undo.

On this page