Open topic with navigation

TIBCO® Patterns 6.1.2

# Joined Searches

The equivalent Java API query in TIBCO Patterns for the example mentioned in Joins Example, is:

    NetricsJoin join_def = new NetricsJoin(“Persons”,
    NetricsJoin.JOIN_FULL_AND_PARTIALS,
                                       false,
                                       null,
                                       String [] { “Addresses”, “Phones” }
                           );
    NetricsQuery q_first_name = NetricsQuery.Simple(
                                     “John”,
                                        new String [] { “Persons.first_name”},
                                     null
                                );
    NetricsQuery q_last_name =  NetricsQuery.Simple(
                                     “Smith”,
                                     new String [] {“Persons.last_name”},
                                     null
                                );
    NetricsQuery q_street =     NetricsQuery.Simple(
                                     “123 Main St.”,
                                     new String [] {“Addresses.street”},
                                     null
                                );
    NetricsQuery q_city =       NetricsQuery.Simple(
                                     “Clarksburg”,
                                     new String [] {“Addresses.city”},
                                     null
                                );
    NetricsQuery q_phone_num =  NetricsQuery.Simple(
                                     “609-884-1010”,
                                     new String [] {“Phones.number”},
                                     null
                                 );
    NetricsQuery query = NetricsQuery.And(null,
                                          new NetricsQuery [] {
                                                    q_first_name,
                                                    q_last_name,
                                                    q_street,
                                                    q_city,
                                                    q_phone_num
                                          }
                         );
    NetricsSearchCfg search_def = new NetricsSearchCfg(join_def);
    search_def.setNetricsQuery(query);

There is no equivalent to the WHERE clause expression of the SQL statement. A joined search must always have a parent table. It must also have at least one child table, and the child table must be a child of the given parent. However, it is not required to list all the child tables of the parent. Any subset of the child tables of the parent can be included in the joined search.

In a joined fuzzy search, the relationship between records is firmly fixed, it is only the matching of values in the records that is fuzzy. The relationship between records is fixed when the records are created.

| • | A fuzzy joined search is always equivalent to a standard fuzzy search on a single table that is a fully denormalized version of the joined tables.

A fully denormalized table has all the fields from each of the individual tables. For example, consider the three tables to have the following records:

Persons Table

id | first_name | last_name | dob | SSN
1 |
    john
    smith
    6/23/1963
    012345678
2 |
    john
    smythe
    5/23/1960
    021345678
3 |
    jim
    myth
    8/14/1984
    918273645

Addresses Table

addr_id | person_id | street | city | state
1 |
    1
    123 Main St
    Clarksburg
    NJ
2 |
    1
    32 Route 33
    Robbinsville
    NJ
1 |
    2
    213 Main St
    Clarksburg
    NJ
3 |
    1
    312 State Rd
    Princeton
    NJ
1 |
    3
    231 Ferry Rd
    Ewing
    NJ

Phones Table

phone_id | person_id | number | type
1 | 1 | 609-833-1001 | home
2 | 1 | 732-981-1100 | cell
1 | 3 | 609-442-0101 | cell
1 |  | 609-833-0011 | none

The fully denormalized table:

Denormalized Table

id | first_ name | last_name | addr_ id | street | phone_ id | number | type
1 | john | smith | 1 | 123 Main St | 1 | 609-833- 1001 | home
1 | john | smith | 1 | 123 Main St | 2 | 732-981- 1100 | cell
1 | john | smith | 2 | 32 Route 33 | 1 | 609-833- 1001 | home
1 | john | smith | 2 | 32 Route 33 | 2 | 732-981- 1100 | cell
1 | john | smith | 3 | 312 State Rd | 1 | 609-833- 1001 | home
1 | john | smith | 3 | 312 State Rd | 2 | 732-981- 100 | cell
2 | john | smythe | 1 | 213 Main St |  |  |
|  |  |  |  | 1 | 609-883- 011 | none
3 | jim | myth | 1 | 231 Ferry Rd | 1 | 609-442- 0101 | cell

There is a separate record for each possible combination of parent and child records. As there were three child records for “John Smith” in the Addresses table and two in the Phones table, you can see 6 separate records for “John Smith” in the de-normalized table. There are two rows that are not fully populated. One is a parent record that has no child in the “Phones” table, and the other is an orphan record in the “Phones” table.

Subtopics

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.