Skip to main content
Back to module
Lab Data & Quality · Records and Integrity

Querying sample data

Reading37mLesson 3 of 3 in this module

Query lab results by sample, batch and date with SELECT, WHERE, JOIN and GROUP BY.

Why it matters

A LIMS stores its records in a relational database, and the questions a lab asks every day are database queries. Which samples from this batch are still untested? What did HPLC-02 measure after its last good calibration? Which results used reagent lot K2291? Most LIMS screens are built on queries like these, and many labs let analysts and QC reviewers run read-only reports in SQL (Structured Query Language). You do not need to be a programmer to read and adapt a simple query. This lesson shows the handful of SQL pieces that cover most lab questions.

Tables and keys

A lab database splits information into tables so each fact is stored once:

  • samples: one row per sample, with sample_id, batch_id, sample_type and received_date.
  • results: one row per result, with result_id, sample_id, analyte, value, units, run_date and instrument_id.

sample_id is the primary key of samples (unique for each sample) and appears in results as a foreign key, linking each result to its sample. One sample can have many results: an assay, a water content, a dissolution test.

SELECT, FROM, WHERE, ORDER BY

SELECT sample_id, value, run_date
FROM results
WHERE analyte = 'assay'
ORDER BY run_date;
  • SELECT lists the columns you want.
  • FROM names the table.
  • WHERE keeps only rows that meet a condition. Text values go in single quotes.
  • ORDER BY sorts the output (add DESC for newest first).

Combine conditions with AND and OR, and use parentheses when you mix them:

WHERE analyte = 'assay' AND (instrument_id = 'HPLC-01' OR instrument_id = 'HPLC-02')

Dates

Many systems store dates as YYYY-MM-DD text, which sorts correctly. A date range uses BETWEEN, which includes both ends:

WHERE run_date BETWEEN '2026-10-01' AND '2026-10-07'

If a column stores a full timestamp (2026-10-07 14:30), BETWEEN '2026-10-01' AND '2026-10-07' misses results from later on the 7th. Use run_date < '2026-10-08' for the upper end in that case.

Joining tables

The batch is stored on the sample, not on the result. To see results by batch, join the tables on the shared key:

SELECT r.sample_id, s.batch_id, r.analyte, r.value
FROM results AS r
JOIN samples AS s ON s.sample_id = r.sample_id
WHERE s.batch_id = 'B-102';

AS r and AS s are short aliases, so r.value means the value column of results. JOIN ... ON pairs each result with its sample row.

Counting and summarising

SELECT s.batch_id, COUNT(*) AS n_results, ROUND(AVG(r.value), 2) AS mean_assay
FROM results AS r
JOIN samples AS s ON s.sample_id = r.sample_id
WHERE r.analyte = 'assay'
GROUP BY s.batch_id;

GROUP BY makes one output row per batch, and COUNT, AVG, MIN and MAX summarise each group.

Worked example: an instrument question

HPLC-02 failed its calibration check on 2026-10-09. The last good calibration was 2026-10-04. Which results need review?

SELECT sample_id, analyte, value, run_date
FROM results
WHERE instrument_id = 'HPLC-02'
  AND run_date >= '2026-10-04'
ORDER BY run_date, sample_id;

This is the kind of query a QC reviewer runs during an investigation. The output is the list of results to assess.

Good habits

  • Read-only. Reporting queries use SELECT only. Changing records (UPDATE, DELETE) in a regulated system happens only through the application, with an audit trail, never by hand in the database.
  • Check the row count. If you expected 3 rows and got 300, a condition is missing.
  • Be exact with text. 'B-102' and 'b-102' may not match, and stray spaces matter.
  • Name your columns in SELECT instead of using SELECT *, so reports stay stable when the table changes.

Common mistakes

  • Forgetting the join condition, which pairs every result with every sample.
  • Using double quotes for text values (in many databases double quotes mean a column name).
  • Mixing AND and OR without parentheses.
  • Forgetting that BETWEEN includes both ends.

Quick recap

  • SELECT columns, FROM a table, WHERE conditions, ORDER BY for sorting.
  • JOIN ... ON links results to samples through sample_id.
  • GROUP BY with COUNT and AVG summarises by batch or instrument.
  • Reporting queries are read-only; changes go through the application and its audit trail.

Try it

Open the Do tab. The query already joins the tables and sorts the rows: you add one WHERE clause to pull one batch's assay results for one week.

Also in this lesson: