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