This topic is under construction.

This topic covers using JOIN in LabKey SQL to bring data together. All data is represented in tables, or queries. A list or dataset or user defined query are all examples of queries. Queries are always defined in a specific folder (container) and schema, but can access information from other queries, schema, and folders using JOINs.

JOIN Columns from Different Tables

Use JOIN to combine columns from different tables.

The following query combines columns from the Physical Exam and Demographics tables.

SELECT PhysicalExam.ParticipantId,
PhysicalExam.weight_kg,
Demographics.Gender,
Demographics.Height
FROM PhysicalExam INNER JOIN Demographics ON PhysicalExam.ParticipantId = Demographics.ParticipantId

Note that when working with datasets in a study, every dataset also has an implied "Datasets" column that can be used in select statements instead of join. See an example in this topic: Various LabKey SQL Examples

Handle Query/Table Names with Spaces

When a table or query name has spaces in it, use "double quotes" around it in your query. For example, if the table name "Physical Exam" had a space in it, the above example would look like:

SELECT "Physical Exam".ParticipantId,
"Physical Exam".weight_kg,
Demographics.Gender,
Demographics.Height
FROM "Physical Exam" INNER JOIN Demographics ON "Physical Exam".ParticipantId = Demographics.ParticipantId

JOIN Data from Different Assays

The following example shows how to join columns from two different assays (ViralLoad and ImmuneScore) so that the columns RNACopies and Measure1 can be compared or visualized.

SELECT 
CASE
WHEN ViralLoad.ParticipantId IS NULL THEN ImmuneScore.ParticipantId
WHEN ViralLoad.ParticipantId IS NOT NULL THEN ViralLoad.ParticipantId
END AS
ParticipantId,
CASE
WHEN ViralLoad.VisitID IS NULL THEN ImmuneScore.VisitID
WHEN ViralLoad.VisitID IS NOT NULL THEN ViralLoad.VisitID
END AS
VisitID,
ViralLoad.RNACopies,
ImmuneScore.Measure1,
FROM ViralLoad
FULL OUTER JOIN ImmuneScore ON
ViralLoad.ParticipantId = ImmuneScore.ParticipantId AND ViralLoad.VisitID = ImmuneScore.VisitID

CROSS JOIN

Cross joins give you all possible combinations between two different tables (or queries).

For example, suppose you have two tables Things and Items:

Thing
A
B

Item
1
2

Then cross join will provide the set of all possible combinations between the two tables:

ThingItem
A1
A2
B1
B2

SELECT Things.Name AS Thing,
Items.Name AS Item
FROM Items
CROSS JOIN Things

JOIN Queries Across Schema

You can join tables from two different schemas by using the syntax "JOIN <SCHEMA>.<TABLE>" to refer to the table in another schema.

For example, suppose you have the following tables, one in the study schema, the other in the lists schema:

study.Demographics

ParticipantIdLanguageGender
PT-101EnglishM
PT-102FrenchF
PT-103GermanF

lists.Languages

LanguageNameTranslatorNameTranslatorPhone
EnglishMonica Payson(206) 234-4321
FrenchEtiene Anson206 898-4444
GermanGundula Prokst(444) 333-5555

The following SQL JOIN creates a table that combines the data from both source tables.

SELECT Demographics.ParticipantId,
Demographics.Language,
Languages.TranslatorName,
Languages.TranslatorPhone,
Demographics.Gender
FROM Demographics
JOIN lists.Languages ON Demographics.Language=Languages.LanguageName

The data returned:

ParticipantIdLanguageTranslatorNameTranslatorPhoneGender
PT-101EnglishMonica Payson(206) 234-4321M
PT-102FrenchEtiene Anson206 898-4444F
PT-103GermanGundula Prokst(444) 333-5555F

JOIN Queries Across Folders

You can join queries from two different containers by adding the server path to the syntax in your JOIN statement. For example, if you had a project named "Other" containing a folder named "Folder" you could use a list in it from another folder by following this example:

SELECT Demographics.ParticipantId,
Demographics.Language,
Languages.TranslatorName,
Languages."PhoneNumber",
FROM Demographics
JOIN "/Other/Folder".lists.Languages ON Demographics.Language=Languages.Language

Learn more about queries across folders in this topic:

Related Topics

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all