My Queries

SampleInfo Query- links sample info into one table to be used by my other queries.

It uses the following tables

 
 

 

The Query is:

  • Sample_dna_id from sample_dna
  • Population from sample_dna
  • Pedigree from sample_dna
  • Name from project
  • Plate_label from grouping (DNA label)
  • Grouping_id from grouping (DNA barcode)
  • Well from sample_dna
  • Submission_id from sample_dna
  • Name from sample_dna (DNA name)
  • Seed_lot from div_stock (Stock identifier)
  • Genus from taxonomy
  • Species from taxonomy
  • Destroy_date from grouping is null






LibraryPlatesWithBarcodeInfo- links library barcodes with the adapter plate barcode to be used with other queries.

The Query is:

  • Grouping_id from sample_prep (library prep barcode)
  • Bc_grouping_id from bc_plate_layout (Adapter plate barcode)

LibraryPlatesWithDNAInfo- I copy this table into Excel and then copy rows out of it to make barcodes.  It also allows me to double check that DNA was correctly assigned to a library prep, barcode plate (I know grouping ID), prep type (again, I know by ID#). The checking is now possible in SampTrac itself, but you still have to know prep type by its ID#.

This table uses the grouping table three times so I had to make the above 2 queries to create it.

It uses the following:



The Query is all unique values from:

  • Prep_type_id from sample_prep (ID for the following prep types: ApeKI, PstI, EcoT22I, PstI/EcoT22I)
  • Grouping_id from sample_prep sorted Ascending (library prep barcode)
  • Project.name from SampleInfo (Project, I don’t use)
  • Plate_label from SampleInfo (DNA plate name)
  • Plate_label from grouping (Library plate name)
  • Grouping_id from SampleInfo (DNA plate barcode)
  • Bc_grouping_id from LibraryPlatesWithBarcodeInfo (Adapter plate barcode)
  • Destroy_date from grouping is Null

DNAPlateStatus – I also copy this into Excel.  This allows me to copy the rows I need to print DNA barcodes before I make a library prep.


The query is:

  • Approval_Status from submission (shows if I’ve approved it from the website)
  • Grouping_id from sample_dna (DNA barcode)
  • Plate_label from grouping (DNA plate name)
  • tag from grouping (not used right now, but can add tags to plates, for a while we had plate name here)
  • submission_date from submission
  • submission_id from submission (shows on website)
  • Destroy_date from grouping (if filled in then ignore entry)

AdapterPlate- I can see the barcode associated with the well number and plate information.

 

 

The query is:

  • Grouping_id from grouping (Adapter barcode)
  • Plate_label from grouping (Adapter plate name)
  • Well from bc_plate_layout
  • Bc_oligo_id from bc_plate_layout
  • Bc_seq  from bc_oligo

FlowCellToPlate- To figure out what flowcell(s) and lane(s) a certain plate was run on.


The query is:

  • Grouping_id from sample_prep (Library barcode)
  • Name from flowcell (Flowcell name)
  • Lane_name from lane
  • Lane_id from lane
  • Plate_label from grouping (Library name)
  • Destroy_date from grouping (checks to make sure I don’t have destroyed plates assigned to flowcell lanes)

 

I also have the following which I’ve used, but I don’t know how much I need them.

ProjectPlateOwnership- Right now Ed is distributing data based on project.  This helped me figure out if sample numbers were correct

FlowCellToSamplePrep- similar to the FlowcellToPlate, but goes all the way to the sample level.

KeyFileName- returned a spreadsheet similar to the “key file” they generate

  • No labels