Introduction to Data Joins

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

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Set the name to Monson University1
4. Click Create
5. On the Ribbon, click File and click Options
6. In the left list, click Current Database
7. In the right list, click Overlapping Windows
8. Click OK
9. On the Ribbon, click External Data and, in the Import & Link section, click Text File
10. In the Get External Data - Text File dialog box, click the Browse button
11. Locate the resources that accompany these lessons and select the Departments.txt file
12. Click Open
13. On the dialog box, click OK
14. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
15. In the second page, accept that the delimiter be set to Tab.
    Click the First Row Contains Field Names check box

    

16. Click Next
17. In the third page of the wizard, click Next
18. Click the arrow of the combo box and select DepartmentCode
19. Click Next
20. Accept the name of the table as Departments and click Finish
21. When the records have been imported, click Close
22. In the Navigation Pane, right-click the Departments table and click Design View
23. Change the following characteristics of the fields:
     

    Field Name	Field Size
    DepartmentCode	5
    DepartmentName	100

24. Close the table
25. When asked whether you want to save, click Yes
26. You will also receive a message box that some data may be lost. Read it and click Yes
27. On the Ribbon, click External Data and, in the Import & Link section, click Text File
28. In the Get External Data - Text File dialog box, click the Browse button
29. From the resources that accompany these lessons, select the Employees.txt file, and click Open
30. On the dialog box, click OK
31. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
32. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names

    

33. Click Next
34. In the third page of the wizard, as EmployeeNumber is selected, click the arrow of the Data Type combo box and select Short Text
35. Click Next
36. Click the arrow of the combo box and select EmployeeNumber
37. Click Next
38. Accept the name of the table as Employees and click Finish
39. When the records have been imported, click Close
40. In the Navigation Pane, right-click the Employees table and click Design View
41. Change the following characteristics of the fields:
     

    Field Name	Field Size	Caption
    EmployeeNumber	10
    FirstName	25	First Name
    MiddleName	25
    LastName	25	Last Name
    DepartmentCode	5
    Title	100

42. Close the table
43. When asked whether you want to save, click Yes
44. You will also receive a message box that some data may be lost. Read it and click Yes
45. On the Ribbon, click External Data and, in the Import & Link section, click Text File
46. In the Get External Data - Text File dialog box, click the Browse button
47. From the resources that accompany these lessons, select the Majors.txt file, and click Open
48. On the dialog box, click OK
49. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
50. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names

    

51. Click Next
52. In the third page of the wizard, click Dean
53. Click the arrow of the Data Type combo box and select Short Text
54. Click Next
55. Click the arrow of the combo box and select MajorID
56. Click Next
57. Accept the name of the table as Majors and click Finish
58. When the records have been imported, click Close
59. In the Navigation Pane, right-click the Employees table and click Design View
60. Change the following characteristics of the fields:
     

    Field Name	Field Size
    MajorID
    Major	100
    Dean	10

61. Close the table
62. When asked whether you want to save, click Yes
63. You will also receive a message box that some data may be lost. Read it and click Yes
64. On the Ribbon, click External Data and, in the Import & Link section, click Text File
65. In the Get External Data - Text File dialog box, click the Browse button
66. From the resources that accompany these lessons, select the Minors.txt file, and click Open
67. On the dialog box, click OK
68. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
69. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names
70. Click Next
71. In the third page of the wizard, click Next
72. Click the arrow of the combo box and select MinorID
73. Click Next
74. Accept the name of the table as Minors and click Finish
75. When the records have been imported, click Close
76. In the Navigation Pane, right-click the Employees table and click Design View
77. Change the following characteristics of the fields:
     

    Field Name	Field Size
    MinrID
    Minor	100

78. Close the table
79. When asked whether you want to save, click Yes
80. You will also receive a message box that some data may be lost. Read it and click Yes
81. In the Navigation Pane, right-click Table1, position the mouse on Import, and click Text File
82. In the Get External Data - Text File dialog box, click the Browse button
83. Locate the resources that accompany these lessons and select the Students.txt file
84. Click Open
85. On the dialog box, click OK
86. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next

    

87. In the second page, accept that the delimiter be set to Comma or Tab and click Next
88. In the second page of the wizard, click First Row Contains Field Names

    

89. Click Next
90. As StudentNumber is selected, click the arrow of the Data Type combo box and select Short Text

    

91. Click Next
92. Click the arrow of the combo box and select StudentNumber

    

93. Click Next
94. Accept the name of the table as Students and click Finish
95. When the records have been imported, click Close
96. In the Navigation Pane, right-click the Students table and click Design View
97. Change the following characteristics of the fields:
     

    Field Name	Field Size	Caption
    StudentNumber	12	Student #
    FirstName	25	First Name
    MiddleName	25
    LastName	25	Last Name
    DateOfBirth
    Gender	20
    Address	120
    City	40
    State	50
    ZIPCode	20	ZIP Code

98. Close the table
99. When asked whether you want to save, click Yes
100. You will also receive a message box that some data may be lost. Read it and click Yes
101. To start a query, on the Ribbon, click Create and click Query Design
102. In  the Show Table dialog box, click Employees, click Add, and click Close
103. In the Students list, double-click EmployeeNumber, FirstName, LastName, and Title
104. Preview the results in the Datasheet View
105. If necessary, on the Ribbon, click Home.
     Change the following characteristics:
     Font Name: Californian FB (if you don't have that font, select Times New Roman)
     Font Color: Blue, Accent 1, Lighter 80% (Theme Colors: 5th column, 2nd row)
     Background Color: Red: 195, Green: 95, Blue: 15
     Gridlines: Horizontal
     Alternate Row Color: More Colors: More Colors: Red: 128, Green: 0, Blue: 0

     

106. Display the SQL View of the query
107. To join the records from another table, change the statement as follows:

     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;

108. Display the Datasheet View of the query

     

109. Close the query
110. When asked whether you want to save, click Yes
111. Set the name to Employees Summary and click OK

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

1. The Monson University1 database should still be opened.
   To start a query, on the Ribbon, click Create and click Query Design
2. In the Show Table dialog box, click Close
3. Display the SQL View of the query
4. To join the records from two other tables, change the statement as follows:

   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;

5. Preview the results in the Datasheet View
6. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Californian FB (if you don't have that font, select Times New Roman)
   Font Color: Blue, Accent 1, Lighter 80% (Theme Colors: 5th column, 2nd row)
   Background Color: Red: 195, Green: 95, Blue: 15
   Alternate Row Color: More Colors: More Colors: Red: 128, Green: 0, Blue: 0

   

7. Close the query
8. When asked whether you want to save, click Yes
9. Set the name to Undergraduate Students and click OK

Fundamentals of Data Joins

Introduction

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:

1. Selecting the tables that will be involved in the join
2. Selecting or designating the column that will participate in the join
3. Linking the shared fields or writing a SQL statement that will create the join

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

1. On the Ribbon, click File and click New
2. Click Blank Desktop Database
3. Set the File Name to Chemistry1 and click Create
4. On the Ribbon, click File and click Options
5. On the left side, click Current Database
6. On the right side, under Document Window Options, click the Overlapping Windows radio button, and click OK
7. Read the message box and click OK
8. On the Ribbon, click File and click Close
9. In the list of files, click Chemistry3
10. To start a new table, on the Ribbon, click Create and click Table Design
11. Type Phase as the first field name and press Tab
12. On the Ribbon, click the Primary Key button
13. Commplete the fields as follows:
     

    Field Name	Data Type	Field Size
    Phase		20
    Description	Long Text

14. Switch the table to Datasheet View
15. When asked whether you want to save, click Yes
16. Set the name to Phases and click OK
17. Create the records as follows:
     

    Field Name	Description
    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.

18. Close the table
19. To start a new table, on the Ribbon, click Create and click Table Design
20. Type AtomicNumber as the first field name and press Tab
21. On the Ribbon, click the Primary Key button
22. Commplete the fields as follows:
     

    Field Name	Data Type	Field Size	Caption
    AtomicNumber	Number		Atomic #
    Symbol		5
    ElementName		20	Element
    AtomicWeight	Number		Atomic Weight
    Phase		20

23. Switch the table to Datasheet View
24. When asked whether you want to save, click Yes
25. Set the name to Elements and click OK
26. Create the records as follows:
     

    Atomic #	Symbol	Element	Weight	Phase
    1	H	Hydrogen	1.00794	Gas
    2	He	Helyum	4.002602	Gas
    3	Li	Lithium	6.941	Solid
    4	Be	Beryllium	9.0121831	Solid
    5	B	Boron	10.811	Solid
    6	C	Carbon	12.0107	Solid
    7	N	Nitrogen	14.007	Gas
    8	O	Oxygen	15.999	Gas
    9	F	Fluorine	18.9984	Gas
    10	Ne	Neon	20.1797	Gas
    11	Na	Sodium	22.98977	Solid
    12	Mg	Magnesium	24.305	Solid
    13	Al	Aluminium	26.98154	Solid
    14	Si	Silicon	28.085	Solid
    15	P	Phosphorus	30.97376	Solid
    16	S	Sulfur	32.06	Solid
    17	Cl	Chlorine	35.45	Gas
    18	Ar	Argon	39.948	Gas
    19	K	Potassium	39.0983	Solid
    20	Ca	Calcium	40.078	Solid

27. Close the table

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. Then:

• If you are just creating the relationship, click Join Type
• If the relationship was ceated already:
  • Right-click its line in the Relationships window and click Edit Relationships. Then click Join Type
  • Double-click its line in the Relationships window and click Edit Relationships. Then click Join Type

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

Inner Joins

Introduction

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

1. On the Ribbon, click Database Tools and click Relationships
2. In the Show Table dialog box, double-click Phases and Elements
3. Click Close
4. Drag Phasse from the Phases list and drop it on Phase from the Elements list

   

5. As seen in Lesson 25, click the Enforce Referential Integrity check box
6. Click the two check boxes below it

   

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

1. In the Edit Relationships dialog box, click Join Type

   

2. In the Join Properties dialog box, make sure the 1 radio button is clicked.
   In the Join Properties dialog box, click OK

   

3. Close the Relationships window
4. Read the message and click Yes
5. Close Microsoft Access