# SQL: Subselect

Enquiry in own or foreign database

### Properties

- Type: [Lookup](https://docs.tsnocode.com/books/field-type-reference/chapter/lookup)
- Groupable: No
- Show in lists: Yes
- Searchable: Yes
- Requires configuration: Yes

## <span class="mw-headline" id="bkmrk-user-interface-1">User interface</span>

<span class="mw-headline">Field in show mode</span>

<span class="mw-headline">\[INSERT IMAGE\]</span>

Due to its nature this field has no special editing mode.

## <span class="mw-headline" id="bkmrk-configuration-1">Configuration</span>

[![image.png](https://docs.tsnocode.com/uploads/images/gallery/2025-07/scaled-1680-/CBG0fsrUWglbpVtj-image.png)](https://docs.tsnocode.com/uploads/images/gallery/2025-07/CBG0fsrUWglbpVtj-image.png)

Required:

- SELECT query that returns a single record with a single value in the recordset

Options:

- Restrictions (when to display the value): 
    - Always: Single, list and subitem mode
    - Items: Single mode only (hidden in list mode)
    - Lists: List mode only (hidden in single mode)
    - Subitem: Lists displayed in single items (child records)

Notes for usage:

- Values are not cached or stored
- Consider activating INDEX for fields used in WHERE or JOIN clauses

### <span class="mw-headline" id="bkmrk-configuration-exampl-1">Configuration example 1</span>

#### <span class="mw-headline" id="bkmrk-solution-setup-1">Solution setup</span>

```
Solution "Child items" (system name: "testchild")
* Relation field "owner" (system name: "PARENT").

Solution: "Parent items" (system name: "testparent")
* Date field "starting date" (system name: "DATO")
```

#### <span class="mw-headline" id="bkmrk-business-requirement-1">Business requirement</span>

Display the starting date (system name: "DATO") from the related parent table, linked by the local field owner (system name: "PARENT").

#### <span class="mw-headline" id="bkmrk-subselect-sql-statem-1">Subselect SQL statement</span>

```mysql
SELECT DATO 
FROM data_testparent 
WHERE :PARENT = data_testparent.DataID
```

this is the shorthand version for

```mysql
...
WHERE data_testchild.PARENT = data_testparent.DataID
```

### <span class="mw-headline" id="bkmrk-configuration-exampl-3">Configuration example 2</span>

#### <span class="mw-headline" id="bkmrk-solution-setup-3">Solution setup</span>

```
Solution "Example" (system name: "example")
* Status log activated
```

#### <span class="mw-headline" id="bkmrk-business-requirement-3">Business requirement</span>

Display the number of state changes for a data item

#### <span class="mw-headline" id="bkmrk-subselect-sql-statem-3">Subselect SQL statement</span>

```mysql
SELECT CONCAT( COUNT(*), ' transitions') 
FROM data_example_statuslog 
WHERE data_example_statuslog.DataID = :DataID
```

this is the shorthand version for

```mysql
...
WHERE data_example_statuslog.DataID = data_example.DataID
```

## <span class="mw-headline" id="bkmrk-developer-info-1">Developer info</span>

- FeltTypeID: 510
- SQL datatype: N/A
- Class name: FieldSubselect