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