Join Columns from Multiple Tables

Documentation
This topic is under construction for the 17.3 release of LabKey Server. For current documentation of this feature, click here.

By combining data from multiple tables in one grid view, you can create integrated grids and visualizations while avoiding duplication of data. The basis of joined grids is a "lookup column" -- a column that connects the two tables such that one table looks up values in another table, making it possible to join data from both tables in combined grids.

Create a Lookup Field

An administrator first needs to connect the two tables by creating a lookup column between the two tables. Lookup columns can be created in two ways: either through the graphical user interface, as described in Lookup Columns; or through a SQL query, as described in Lookups: SQL Syntax.

Create a Joined Grid View

Once tables are connected by a lookup column, you can create a joined grid view on either table, subject to key complexity rules described below.

For example, suppose you wish to create a grid that brings together columns from both the Demographics and the Languages tables. A lookup column has already been defined in the Demographics table that looks up data in the Language table. To create a grid including data from both tables:

  • Go to the Demographics data grid.
  • Select (Grid Views) > Customize Grid.
  • In Available Fields, expand the Languages node -- this reveals the available columns in the Languages table.
  • To add a column from the Languages table, place a checkmark next to it. Below two columns have been added: Translator Name and Translator Phone. You may need to drag and drop them to the desired order. Here, directly after the Languages column that was already displayed.
  • The columns will be added to Selected Fields.
  • Save the grid, which now includes columns of data from the Languages table.

Troubleshooting

FAQ: In a study, why can't I customize my grid to show a particular field from another dataset?

Background: To customize your grid view of a dataset by adding columns from another dataset, it must be possible to join the two datasets. The columns used for a dataset's key influence how this dataset can be joined to other tables. Certain datasets have more than one key column (in other words, a "compound key"). In a study, you will typically encounter three types of datasets:

  • Demographic datasets use only one column as a key. The key is the participantID. This means that only one line of data (for any date) can be associated with a participant in such a dataset.
  • Clinical or standard datasets use participant/visit pairs as a compound key. This means that each row is uniquely identified by participant/visit pairs, not by participant identifiers alone.
  • Assay datasets copied into a study also use compound keys. Assay datasets use participant/visit/rowID columns as compound keys, so only the trio of these columns uniquely identifies a row. In other words, a participant can have multiple assay rows associated with any individual visit - these are uniquely differentiated only by their rowIDs.
Consequences: When customizing the grid for a table, you cannot join in columns from a table with more key columns. For example, if you are looking at a clinical dataset in a study, you cannot join to an assay dataset because the assay dataset has more columns in its key, and thus can have multiple rows per participant/visit. So there isn't a unique mapping from a participant/visit pair in the 'originating' clinical dataset to a specific row of data in the assay dataset.

Guidance: To create a grid view combining columns from disparate datasets, start with the dataset with more columns in the key. Then select a column from the table with fewer columns in the key. There can be a unique mapping from the compound key to the simpler one - some columns will have repeated values for several rows, but rows will be unique.

Related Topics

Discussion

previousnext
 
expand all collapse all