Lookups: SQL Syntax

2024-03-28

Lookups are an intuitive table linking syntax provided to simplify data integration and SQL queries. They represent foreign key relationships between tables, and once established, can be used to "expose" columns from the "target" of the lookup in the source table or query.

All lookups are secure: before execution, all references in a query are checked against the user's security role/permissions, including lookup target tables.

This topic describes using lookups from SQL. Learn about defining lookups via the user interface in this topic: Lookup Columns.

Lookup SQL Syntax

Lookups have the general form:

Table.ForeignKey.FieldFromForeignTable

Examples

A lookup effectively creates a join between the two tables. For instance if "tableA" has a lookup to "tableB", and "tableB" has a column called "name" you can use this syntax. The "." in the "tableB.name" does a left outer join for you.

SELECT *
FROM tableA
WHERE tableB.name = 'A01'

Datasets Lookup

The following query uses the "Datasets" special column that is present in every study dataset table to lookup values in the Demographics table (another dataset in the same study), joining them to the PhysicalExam table.

SELECT PhysicalExam.ParticipantId,
PhysicalExam.date,
PhysicalExam.Height_cm,
Datasets.Demographics.Gender AS GenderLookup,
FROM PhysicalExam

It replaces the following JOIN statement.

SELECT PhysicalExam.ParticipantId,
PhysicalExam.date,
PhysicalExam.Height_cm,
Demographics.Gender AS GenderLookup
FROM PhysicalExam
INNER JOIN Demographics ON PhysicalExam.ParticipantId = Demographics.ParticipantId

Lookup From a List

The following expressions show the Demographics table looking up values in the Languages table.

SELECT Demographics.ParticipantId, 
Demographics.StartDate,
Demographics.Language.LanguageName,
Demographics.Language.TranslatorName,
Demographics.Language.TranslatorPhone
FROM Demographics

It replaces the following JOIN statement.

SELECT Demographics.ParticipantId, 
Demographics.StartDate,
Languages.LanguageName,
Languages.TranslatorName,
Languages.TranslatorPhone
FROM Demographics LEFT OUTER JOIN lists.Languages
ON Demographics.Language = Languages.LanguageId;

Lookup User Last Name

The following lookup expression shows the Issues table looking up data in the Users table, retrieving the Last Name.

Issues.UserID.LastName

Discover Lookup Column Names

To discover lookup relationships between tables:

  • Go to (Admin) > Developer Links > Schema Browser.
  • Select a schema and table of interest.
  • Browse lookup fields by clicking the icon next to a column name which has a lookup table listed.
  • In the image below, the column study.Demographics.Language looks up the lists.Languages table joining on the column LanguageId.
  • Available columns in the Languages table are listed (in the red box). To reference these columns in a SQL query, use the lookup syntax: Demographics.Language."col_in_lookup_table", i.e. Demographics.Language.TranslatorName, Demographics.Language.TranslatorPhone, etc.

Note that the values are shown using the slash-delimited syntax, which is used in the selectRows API. API documentation is available here:

Adding Lookups to Table/List Definitions

Before lookup columns can be used, they need to be added to the definition of a dataset/list/query. The process of setting up lookup relationships in the field editor is described here: Lookup Columns.

Creating a Lookup to a Non-Primary-Key Field

When you select a schema, all tables with a primary key of the type matching the field you are defining are listed by default. If you want to create a lookup to a column that is not the primary key, you must first be certain that it is unique (i.e. could be a key), then take an additional step to mark the desired lookup field as a key.

You also use the steps below to expose a custom SQL query as the target of a lookup.

  • First create a simple query to wrap your list:
    SELECT * FROM list.MyList
  • You could also add additional filtering, joins, or group by clauses as needed.
  • Next, annotate the query with XML metadata to mark the desired target column as "isKeyField". On the XML Metadata tab of the query editor, enter:
    <tables xmlns="http://labkey.org/data/xml">
    <table tableName="MyList" tableDbType="NOT_IN_DB">
    <columns>
    <column columnName="TargetColumn">
    <isKeyField>true</isKeyField>
    </column>
    </columns>
    </table>
    </tables>
  • Save your query, and when you create lookups from other tables, your wrapped list will appear as a valid target.

Related Topics