Inner and outer joins

Description: This tutorial briefly explains the two most common ways to combine data from more than one table, INNER JOIN and LEFT OUTER JOIN

Program Used: Microsoft Office – Access

Skill Level: Basic

INNER JOIN Commonly referred to just as JOIN is the default join type. It produces a result set that is limited to the rows where there is a match in both tables.

LEFT OUTER JOIN A join that results in a set where all of the rows from the table on the left hand side (side without the arrow) are preserved. The rows from the other table only show up if they have a match with the rows from the first table. Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set.

Contents

  1. Query Setup
  2. Filters
  3. Ex1. INNER JOIN
  4. Ex2. LEFT OUTER JOIN

Query Setup

  1. In Microsoft Access navigate to the Create tab and select Query Design.
  2. Search through your table listings on the left and pull dbo_htv_entity, dbo_htv_major_program, dbo_htv_stdt_demographics, and dbo_htv_grad_master into the Query1 design window.
  3. Join the entity table to the other 3 tables by clicking and dragging the ID field from the entity table to the ID field in the other table.
  4. Add the following fields
    1. From dbo_htv_entity: ID, LastName, PreferredName_Substitute, TestPerson
    2. From dbo_major_program: ProgramCode, PriveJointDiv, Status
    3. From dbo_htv_stdt_demographics: FrozenCohort
    4. From dbo_htv_grad_master: DispSem, OSRStatus

Filters

Adding Criteria to fields to filter the data

  1. ProgramCode: Like “BU%” (Division portion of the program code)
  2. PrimeJointDiv: = “P” (for prime division)
  3. Status: = 1 (Represents open programs only)
  4. FrozenCohort: = “FR2016BU” (FR represents fall entering freshmen, 2016 is expected grad year, BU primary division on day one.)
  5. DispSem:
    1. = “SP2016” – (For Ex1. INNER JOIN – Semester student filed an ITG)
    2. = “SP2016” or Is Null – (For Ex2. LEFT OUTER JOIN – Semester student filed an ITG or Missing ITG record)
  6. TestPerson: <> “Y” – (exclude test accounts that may be active such as George Washington)


Your final setup should look similar to the image below.
You can uncheck the Show checkboxes for TestPerson, PrimeJointDiv and Status if you only want to filter on them and don’t want them displayed in your report

Ex1. INNER JOIN

In our first example we will use the INNER JOIN to combine our tables based on the Student’s ID to return a list of current Business Undergrads (BU) students that entered as BU freshmen, are in the 2016 cohort, and have filed an Intent To Graduate for May 2016.

Ex2. LEFT OUTER JOIN

Now lets say you want to add to the report students that haven’t filed an ITG.

  1. Start by double click on the join line that goes from dbo_htv_entity to dbo_htv_grad_master
    1. This will open the Join Properties dialog box.
  2. Change the selection from 1: to 2: and click OK
  3. Since we are filtering on a field from the grad master table we have one more edit
    1. The DispSem criteria filter to read “SP2016” Or Is Null.
    2. The Is Null is what will give us the students that don’t have a SP2016 record, but met the rest of the criteria.

You will notice the join line now has an arrow pointed to the dbo_htv_grad_master table. This means that we will now get all of the students from the FR2016BU frozen cohort that are current prime in BU regardless of their ITG filing.

February 2016 SIS Reporting Database Newsletter – RCroft