Lookups are an intuitive table linking syntax provided to simplify data integration and SQL queries. 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. 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:


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.


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

SELECT Demographics.ParticipantId, 
FROM Demographics

It replaces the following JOIN statement.

SELECT Demographics.ParticipantId, 
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">
    <column columnName="TargetColumn">
  • Save your query, and when you create lookups from other tables, your list will appear as a valid target.

Related Topics


Was this content helpful?

Log in or register an account to provide feedback

expand all collapse all