Open topic with navigation

TIBCO® Patterns 6.1.2

# Joined Records

The result of a joined search is a joined record. In SQL, the fields to be returned are specified in the SELECT clause. But in TIBCO Patterns, all the fields of a record are always returned. There is no “SELECT” mechanism for selecting fields to be returned. This is true for standard searches as well as joined searches. In a joined search, all the fields from all tables are always included for every record that is returned. It behaves as if they were records from the fully denormalized table, including the special parent record key fields and the record keys from each child table.

In the example mentioned, the TIBCO Patterns joined records would have the following fields:

Joined Records

`Sample Values`
`field_name` | `Record 1` | `Record 6` | `Record 7` | `Record 8`
`key` | `1<tab>1<tab>1` | `1<tab>3<tab>2` | `2<tab>1<tab>` | `<tab><tab>1`
`parent-record-key` | `1` | `1` | `2` |
`Persons.first` | `john` | `john` | `john` |
`Persons.last` | `smith` | `smith` | `smythe` |
`Persons.dob` | `6/23/1963` | `6/23/1963` | `5/23/1960` |
`Persons.SSN` | `012345678` | `012345678` | `021345678` |
`Addresses.` | `1` | `3` | `1` |
`Addresses.^parent` | `1` | `1` | `2` |
`Addresses.street` | `123 Main St.` | `312 State Rd` | `213 Main St` |
`Addresses.city` | `Clarksburg` | `Princeton` | `Clarksburg` |
`Addresses.state` | `NJ` | `NJ` | `NJ` |
`Phones.` | `1` | `2` |  | `1`
`Phones.^parent` | `1` | `1` |  |
`Phones.number` | `609-883-1001` | `732-981-1100` |  | `609-883-0011`
`Phones.type` | `home` | `cell` |  | `none`

Description of Terms

Term | Description
key | This is the unique key value for the joined record. It is not a data field. Hence, not included in the list of field names. A joined record has the same structure as any other record. This includes having a unique key value. For a joined record, the unique key value is the key value of each of the component records separated by the tab character (represented in the samples by “<tab>”). It is an ordered set with the parent table first, followed by each child table, in the order specified in the join definition for the search. If a record for a table does not exist, there is no key value, but the “<tab>” separators are still included. This can be seen in the Record 7 and Record 8 sample values.
parent-record-key | It is the special parent record key that is included with all child records. Like the key, it is not a data field in the record and does not have a field name. A joined record is returned as a child record if it contains any child records. The parent record key value is the parent record key value of the child record. Note that all child records in a joined record will always have the same parent record key value. If a joined record does not contain any child records, it is returned as a standard record. This standard record does not contain a parent record key value. Note that Record 8 does not have a parent record key value. This contains a single orphan record from a child table, but the record is a standard record without a parent record key. So the returned joined record is a standard record without a parent record key. If the orphan record was a child record with a parent key, the returned joined record would be a child record and contain a parent record key value also.
Persons.first, Persons.last, Persons.dob, Persons.SSN | Data fields from the Persons table
Addresses. | This is a special data field that contains the key value of the Addresses record. The key value of each child record is included as a data field in the returned record with a field name that consists of the table name (or table alias) followed by a period. It has no field name parameter. The individual record key values can be retrieved using these special fields if required. Although this appears in the returned joined record as a data field, it cannot be used as a field in a search query.
Addresses.^parent | This is the special parent record key field discussed in the section The special parent key field name. It contains the parent record key value from the child record. As mentioned earlier, this field is accessible as a data field in a query. But as it is considered a text-only field and not a searchable text field, it can only be used in predicate expressions.
Addresses.street, Addresses.city, Addresses.state | Data fields from the Addresses child table
Phones. | This is the special data field containing the key value of the Phones record. Note that Record 7 has no value for this field. For a child table, if no record is included in the joined record, this field is empty. As mentioned earlier, all fields are always included in the returned record, even if no record is included for that table. You can find the difference between a field that is empty because the record had no data for that particular field and a field that is empty because no record for that table was included by checking the associated key field value. If it is empty, no record was included (a record must have a non-empty key). If it is populated, the record is included, but had no data for that field.
Phones.^parent | This is the special parent record key field for the “Phones” table. Note that for Record 8 this field has no value. Depending on the join type, standard records can be returned in a search as orphan records. Standard records have no parent record key value and always appear as orphan records.
Phones.number, Phones.type | Data fields for the “Phones” table

## Classification of Joined Records

A joined record can be classified into 3 types:

| 1. | Full Record A full record is one that contains a record from each of the tables in the joined search. In the previous example Records 1 and 6 are full records.

| 2. | Partial Record A partial record is a record that has no records for one or more of the child tables in the joined search.

Record 7 mentioned in the Joined Records table is a partial record.

| 3. | Orphan Record An orphan record contains no parent record. As records are linked through the parent record, you cannot link two or more child records without a parent record. Therefore, an orphan joined record always contains exactly one child record. Record 8 in the previous example is an orphan record.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO® Patterns

Copyright © 2010-2025. Cloud Software Group, Inc. All Rights Reserved.