With SQL reports all data of the LDB is available to you. This is a powerful tool to output all data in the desired form. Therefore, the basics are taught in the form of training to unlock this function.

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 (LDB internal variable, see instructions on Variables in query reports). 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.

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