Open topic with navigation

TIBCO® Patterns 6.1.2

# Joins Example

A joined search involves querying or retrieving data from fields across multiple tables.
Let us consider three tables:

Tables | Fields
Persons |
    first_name, last_name, dob, SSN
Addresses |
    street, city, state
Phones |
    number, type

A joined search across the three tables is used to perform the following searches:

| • | Retrieving all records with name "John", "Smith", who lives at "123 Main St.", "Clarksburg", and with phone number "609-883-1010."

| • | Retrieving addresses and phone numbers of records with the name "John", "Smith", born on "June 22, 1963."

The second search queries only one table but retrieves data from three separate tables. Therefore, a joined search is needed.

To perform these searches, there must be a connection that ties the three tables together. The most common approach in SQL are to provide a foreign key field in each child table. The contents of the foreign key field is the unique key value for the parent record. For our example, let us assume the unique key for the "Persons" record is contained in a field named "id" in the Persons table. We assume the "Addresses" and "Phones" table each have foreign key fields named "person_id". We also assume the "Addresses" and "Phones" table have unique key fields of their own named "addr_id" and "phone_id" respectively.

An SQL statement to perform the joined search is:

    SELECT * FROM Persons, Addresses, Phones
    	 WHERE Persons.id = Addresses.person_id AND Persons.id = Phones.person_id
    	  AND Persons.first_name = “John” AND Persons.last_name = “Smith”
    	  AND Addresses.street = “123 Main St.”
    	  AND Addresses.city = “Clarksburg”
    	  AND Phones.number = “609-833-1010”

The `FROM` clause gives a list of tables involved.
The `WHERE` clause gives the linking between the records.

In TIBCO Patterns this linking is done when tables and records are created, unlike in SQL, where it is done at query time.

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.