Lookups: SQL Syntax: /Documentation/Archive/19.3

Lookups: SQL Syntax

Lookups simplify data integration and SQL queries with an intuitive table linking syntax. LabKey Server understands foreign key columns as "lookups" to columns in other tables and provides a syntax to capture this relationship. Also note that lookups are secure -- before execution, all references in a query are checked against the user's security role/permissions, including lookup target tables.

Lookup SQL Syntax
Examples
Discover Lookup Column Names
Adding Lookups to Table/List Definitions

Lookup SQL Syntax

Lookups have the general form:

Table.ForeignKey.FieldFromForeignTable

Example #1

The following query uses the Datasets table to lookup values in the Demographics table, joining them to the Physical Exam table.

SELECT "Physical Exam".ParticipantId,
"Physical Exam".date,
"Physical Exam".Height_cm,
"Physical Exam".Weight_kg,
Datasets.Demographics.Gender AS GenderLookup,
FROM "Physical Exam"

It replaces the following JOIN statement.

SELECT "Physical Exam".ParticipantId,
"Physical Exam".date,
"Physical Exam".Height_cm,
"Physical Exam".Weight_kg,
Demographics.Gender AS GenderJoin
FROM "Physical Exam" 
INNER JOIN Demographics ON "Physical Exam".ParticipantId = Demographics.ParticipantId

Example #2

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

Issues.UserID.LastName

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;

Other lookup examples:

...
WHERE VialRequest.Request.Status.SpecimensLocked 
  AND VialRequest.Vial.Visit.SequenceNumMin = ClinicalData.SequenceNum 
  AND VialRequest.Vial.ParticipantId = ClinicalData.ParticipantId
...

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. For details on using the query API, see LABKEY.Query.

Adding Lookups to Table/List Definitions

Before lookup columns can be used, they need to be added to definition of a dataset/list. The process of setting up lookup relationships in the property designer 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.

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 list will appear as a valid target.

LabKey Support

LabKey Support