1. Basic Building Block
A SQL report consists of an SQL code with the following content:
SELECT
samples.name AS 'Sample',
samples.description AS 'Description'
FROM samples
WHERE samples.visible_to != 'deleted'
ORDER BY samples.name DESC;


2. Elements
The following elements are commonly used:

SELECT: Columns
AS: Column-Description (optional)
FROM: Data source table
WHERE: Conditions
ORDER BY: Sorting

3. Query Building Blocks
You can use the following query building blocks in your SQL reports:

WHERE samples.visible_to != 'deleted' Excludes deleted samples.

WHERE samples.date BETWEEN FROMDATE AND TILLDATE Allows the entire evaluation to be filtered by date ranges (laboratory database internal variable, see instructions on LINK_TO_PAGE=234). The filter is displayed above the SQL Report.

ORDER BY samples.date DESC Sorts in descending (DESC) order by sample date (or ASC for ascending).

For more examples of evaluations, refer to: Evaluations -> Examples

4. JOIN
To join multiple tables together, you need a JOIN.
code>SELECT FROM oldTabs
…
JOIN newTabs ON ordTabs.id = newTabs.ordTab_id;

Note: In the LAB DATABASE, all table names are in plural (Tables), while column names are mostly in singular (Table). This makes it easier to distinguish between table names and column names.

Here is a small overview of which tables are linked for the areas: Order-Sample-Result-Parameter-Specification.

5. Link
To obtain links in the result table, use the laboratory database's internal linking system. For example, to link to the sample details view, use:

CONCAT('link==../samples/view/', samples.id, '==', samples.name) as 'Samples'
../samples/view/' = LDB-internal Link
samples.id=Part of the link
samples.name = Display value
'Proben' =Column header

6. Linked Master Data Fields
For example, if a master data field in samples (DDF_CUSTOMER) is linked to customer numbers, use the following SELECT/JOIN:
customers.name as 'customer'
FROM samples
JOIN customers ON samples.DDF_KUNDE = customers.id

7. Functions