Home

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 LearningPractical 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 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

    Importing a Text Document

  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 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

    Importing a Text Document

  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 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

    Importing a Text Document

  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 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 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

    Import Text Wizard

  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

    Importing a Text Document

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

    Importing a Text Document

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

    Importing a Text Document

  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

    Using a Shared Field to Join Tables

  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

    Using a Shared Field to Join Tables

  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 LearningPractical 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

    Using a Shared Field to Join Tables

  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 LearningPractical 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 Primary Key
  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 Primary Key
  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:

Join Properties

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

    Creating Data Relationships

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

    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 LearningPractical Learning: Creating an Inner Join

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

    Join Properties

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

    Creating Data Relationships

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

Previous Copyright © 2000-2016, FunctionX, Inc. Next