Retrieve all primary screen

all-primary-screen.sql

## To get back all of the primary screen first pass interesting experiment images for Amalia and Victoria

## Select the fields of interest
SELECT 
    s.screen_name,
    # a.assay_code_name,                       # the assay itself (not needed, redundant with library plate coordinates)
    a.assay_image_path,
    l.ReagentName,                             # the reagent used
    l.MasterPlateWell, l.StockPlateWell,       # library coordinates for the reagent
    # m.treatment_group_id,                    # treatment group (ignore for now)
    # m.manualscore_code, manualscore_value,   # include score values if you want everything
    m.user_name, m.timestamp                   # who scored it, and when 

INTO OUTFILE 'boxem_results.txt'
## Match IDs across all the relevant tables needed to retrieve the data
FROM exp_screen AS s,
     exp_manual_scores AS m,
     exp_assay AS a,
     exp_assay2reagent AS r,
     rnai_library AS l                         # assume reagent is RNAi for now
WHERE s.screen_id = m.screen_id
AND m.assay_id = a.assay_id
AND a.assay_id = r.assay_id
AND r.reagent_id = l.rnai_id

## Find the right screen
AND (s.screen_id = 12 OR                       # looking for the lgl-1 screen
     s.screen_id = 14)

## Get all the experiments that looked interesting
AND m.manualscore_group = "FIRST_PASS"
AND m.manualscore_value = 1                    # comment this out if you want everything

AND (a.assay_image_path LIKE "%_am%"
OR a.assay_image_path LIKE "%_vi%")


GROUP BY a.assay_image_path                    # Will get all of the experiments/replicates/duplicates
ORDER BY l.StockPlateWell ASC;