Lookups: SQL Syntax: /home/

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

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.

Lookup Column Administration - Adding Lookups to Table/List Definitions

Before lookup columns can be used, they need to be added to definition of a dataset/list. For details on setting up lookup relationships, see Lookup Columns.

LabKey Support

LabKey Support