Introduction to C
A Shared Field to Join Tables
Using a Shared Field to Join Two Tables
One of the most important features of a relational database consists of combining records from various tables to get a single list. The SQL provides two main options: Applying a condition on a common field or creating a join.
The primary way to join two tables to create a common list that combines their records is to match the records they have in common. Before doing this, the lists must have a field used as the primary key on one table and a foreign key on the other table. The formula to follow is:
SELECT Field(s) [, Field(s)] FROM Table1, Table2 WHERE Condition
You use a SELECT statement to select fields from one or all tables, then you use a WHERE condition to specify how the records will be matched. The condition should be in the form:
primary-key = foreign-key
Because the primary key and the foreign key may have the same names, you should qualify their names.
Practical Learning: Using a Shared Field to Join Tables
SELECT Employees.EmployeeNumber AS [Employee #], Employees.FirstName AS [First Name], Employees.LastName AS [Last Name], Employees.Title, Departments.DepartmentName AS Department FROM Employees, Departments WHERE Employees.DepartmentCode = Departments.DepartmentCode;
Clerk: [EmployeeNumber] & ": " & [EmployeeName]
SELECT ShoppingSessions.ReceiptNumber, [EmployeeNumber] & ": ": & [EmployeeName] AS Clerk, ShoppingSessions.ShoppingDate, ShoppingSessions.ShoppingTime, ShoppingSessions.SalesTotal, ShoppingSessions.AmountTendered FROM ShoppingSessions, Employees WHERE ShoppingSessions.StaffMember = Employees.EmployeeNumber;
Creating a Join Using Various Shared Fields
If a table shared fields with more than one other table, you can used their shared fields to join them. The formula to follow is:
SELECT Field(s) [, Field(s)] FROM Table1, Table2 WHERE condition1 AND condition2
You use the logical conjunction operator AND to concatenate the conditions.
Practical Learning: Creating a Join Using Various Shared Fields
SELECT StudentNumber AS [Student #], FirstName AS [First Name], LastName AS [Last Name], DateOfBirth AS DOB, Year(Date()) - Year(DateOfBirth) AS Age, Gender, Major, Minor FROM Students, Majors, Minors WHERE Students.MajorID = Majors.MajorID AND Students.MinorID = Minors.MinorID;
Fundamentals of Data Joins
A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:
The Tables of a Join
Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. If needed, you can then create the necessary records for the table.
When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table.
Practical Learning: Creating the Tables of a Data Join
|Field Name||Data Type||Field Size|
|Solid||Solid is the stiff and hard state that characterizes matter. It means that the material cannot physically be bent. It cannot let gas or liquid through.|
|Gas||Gas is a chemical that specifies that a matter is primarily made of one type of molecule made of one or more atoms. While the components (molecules) of a solid are tight (or tightly bound), those of a gas can be separate but still maintaining a gaseous aspect.|
|Field Name||Data Type||Field Size||Caption|
Visually Creating a Data Join
After creating the necessary tables and their columns, you can create the data join. You can do this in the Relationships window or when building a query. A data join created in the Relationships window will be part of the database.
To create a data join in the Relationship window, first create or start a data relationship as we saw in Lesson 3. Then:
Any of these actions would display the Join Properties dialog box:
Creating a Data Join in the SQL
In the SQL, the basic formula to create a join is:
SELECT field-name(s) FROM child-table join-type parent-table ON condition
The child-table specifies the table that holds the records to be retrieved. It can be represented as follows:
SELECT field-name(s) FROM Persons join-type parent-table ON condition
The parent-table specifies the table that holds the column with the primary key that will control what records, related to the child table, must be used. This would be represented as follows:
SELECT field-name(s) FROM Persons join-type Genders ON condition
The condition is a logical expression used to validate the records that will be isolated. The condition can be created using the following formula:
Table1Column Operator Table2Column
To create the condition, you start with the ON keyword. You can assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:
SELECT field-name(s) FROM Persons join-type Genders ON Persons.GenderID = Genders.GenderID
Although we used the assignment operator "=", another operator, such as LIKE, can also be used, as long as it can be used to assign one column to another. Here is an example:
SELECT field-name(s) FROM Persons join-type Genders ON Persons.GenderID LIKE Genders.GenderID
The field-name(s) of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:
SELECT * FROM Persons join-type Genders ON Persons.GenderID = Genders.GenderID
In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:
SELECT LastName, FirstName, Gender FROM Persons join-type Genders ON Persons.GenderID = Genders.GenderID
If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name. Here is an example:
SELECT LastName, FirstName, Persons.GenderID, Genders.GenderID, Gender FROM Persons join-type Genders ON Persons.GenderID = Genders.GenderID
In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:
SELECT Persons.LastName, Persons.FirstName, Persons.GenderID, Genders.GenderID, Genders.Gender FROM Persons join-type Genders ON Persons.GenderID = Genders.GenderID
When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major role when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables, the SQL supports three types of joins.
Practical Learning: Creating Data Relationships
Creating an Inner Join
Imagine you have two tables that can be linked through one's primary key and another's foreign key. You may have some records in the child table that don't have a value for the primary key of the parent table. Such records may have an empty field. When creating a relationship or a query, if you want the result to include only the records that have a value, you can create the relationship known as an inner join.
To visually create an inner join in the Relationships window, simply drag the primary key from the parent table and drop it on the foreign key in the child table as seen in Lesson 3.
To destroy a join between two tables, if you are working in the Relationships window, right-click the line that joins the tables and click Delete.
Practical Learning: Creating an Inner Join
|Previous||Copyright © 2000-2019, FunctionX, Inc.||Next|