Matched samples query

Example: Lung cancer patients who have had lung tumor specimens collected AND non-lung tumor specimens collected.

This can be achieved using a subquery as follows:

1. Select Queries from Dashboard and Click on '+Create'.
   
2. To add condition for anatomic site, click on Specimen and select 'Anatomic Site'. Add filter with Operator 'Equals' and select 'Lung, NOS' as a value.
   
   
   
3. To define type of specimens, click on Specimen and select 'Type'. Add 'Frozen Tissue' under the value.
   
   
4. Since these types of samples are already collected, click on 'Collection Status' and select 'Collected' as the condition value.
   
   
   
5. To define tissue type which is cancer in this case, click on Specimen and select 'Pathological Status'. Add filter with Operator 'Equals' and select 'Malignant' under the condition value.
   
   
6. Click on "View Records". From the Actions > Columns, select the PPID, Visit Name and Specimen Label column in the results view. Click on "Done"
   
   
7. Go to Actions > Save. Give a name to query
   
   
8. Similarly, create another query for non-lung tumour specimens. Click on Queries > Create.
9. Add the filters 'Anatomic Site Not Equals Lung, NOS', 'Specimen Type Equals Frozen Tissue' and 'Collection Status  Equals Collected' from the 'Specimen' filter
   
   
10. Click "View Records". From 'Actions' > 'Columns' select the columns PPID, Visit Name and Specimen Label. Click on 'Done' and save the query
    
11. Now, create another query to select the records that match both of these conditions. Click 'Create'.
12. Add the condition PPID Is One Of and check the checkbox for 'Records of another subquery'. In the select query dropdown, select the query 'Patients with lung tumour tissues'. Click on 'Add'
    
    
13. Similarly add a condition PPID Is One Of Patients with non-lung tumour specimens. Click on 'Add'.
    
    
14. This will show all specimens of participants belonging to these conditions. 
15. Refer step 12 to add filters for including only lung tumor frozen tissues and non-lung tumor frozen tissues. Select Specimen Label Is One Of Patients with lung tumor tissues. Add it. Add another filter for Specimen Label Is One Of Patients with non-lung tumor specimens
    
    
    
16. Now, in the expression above, click on the 'and' between filters 3 and 4 so that it changes to 'or'. Enclose the expression '3 or 4' in parentheses, as below
    
    
    
17. The query for showing lung and non-lung tumor specimens is ready. Save it by clicking "Save" and giving it a title.
    

The main query and subqueries can be imported from here:

Patients with lung tumor tissue subquery

Patients with non-lung tumor tissue subquery

Main query

Import the subqueries first and then the main query. More information on parameterizing subqueries: Subqueries