Get image filepaths for Boxem's images

boxem-image-paths.sql

# get JUST the image file paths for
# matching RNAi controls for "FIRST-PASS INTERESTING" 
# from am and vi screens (12 and 14)

SELECT b.assay_image_path

FROM  exp_assay a, 
      exp_assay b,  
      exp_design d,
    exp_manual_scores AS m,
    exp_assay2reagent AS r,
    rnai_library AS l

WHERE a.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

  AND a.screen_id = d.screen_id
  AND b.screen_id = d.screen_id
  AND a.exp_group_id = d.treatment_group_id
  AND b.exp_group_id = d.control_group_id
  AND d.control_group_reagent_type = "ctrl_rnai"

  AND (a.screen_id = 12
        OR a.screen_id = 14)

GROUP BY b.assay_code_name
ORDER BY b.assay_image_path

INTO OUTFILE "/var/lib/mysql-files/boxem_results.txt";