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 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 is 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 ibi Patterns - Search this linking is done when tables and records are created, unlike in SQL, where it is done at query time.