HOW TO CREATE JOIN QUERIES IN MS ACCESS & MySQL– OBJECT JOINS OR RELATIONSHIPS

When you include multiple data sources in a query, you use JOINS to limit the records that you want to see based on how the data sources are related to each other. You must use JOINS to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results. JOINS in MS Access is just the same as any other Relational Database Management System (RDBMS) like MySQL
JOIN QUERIES in MS Access and MySQL databases

You can also join queries in the same way that you join tables and can also join both tables and queries. Joins behave similarly to query criteria in that they establish rules that the data must match before they are included in the query operations. Unlike criteria, joins also specify that each pair of rows that satisfy the join condition will be combined in the record set to form a single row.

I explained table joins or relationship in chapter 2 (part 2). You may need to revise it because it is a prerequisite to Join Queries.

This is part 4 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to create a JOIN Query in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions. I will also explain object joins or relationship and then give you some practical exercises.

In the previous part (Part 3), I explained UNION query SQL in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

TYPES OF QUERY JOINS

There are many types of query joins, but I will discuss only the five most common JOIN types. They include:
1. Inner Join
2. Left Outer Join
3. Right Inner Join
4. Full Outer Join
5. Cross Join

NOTE:

To create query joins in MS Access:
Create an empty SELECT query through the Query Design option. Then switch the empty SELECT query over to SQL view where you can type your SQL statements directly.

INNER JOIN:

Inner Join shows only rows where a common value exists in both of the joined tables. They are the most common type join. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be returned. For example, the tables below are used for storing information of emergency patients and those of regular patience in a Health Clinic. The first table is named emergency_patients and the second, regular-patients
Emergency patients table

Regular patients table
Referring to the two tables above, if you want to show the ID and HOME TOWN fields and return only the records of emergency patients that are also regular patients, then your inner join query SQL would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E INNER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns four records that appear in both tables as shown below.
Also note how I aliased the table names. Note also, the ON clause used here in place of the WHERE clause, you have Join Queries.
Inner join query result

LEFT OUTER JOIN:

When a query with a left outer join is run, it returns all the records from the first (left) table and the corresponding records from the second table that have a matching value in the join field of the first table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records of emergency patients, including the records of regular patients that are emergency patients, then your left join SQL statement would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns 6 records as shown below.
Left outer join query result
Two records returned in the screenshot above have empty data under the HOME TOWN field because the emergency_patients table don’t have the HOME TOWN field. So the query returned empty spaces.

RIGHT OUTER JOIN:

When a query with a right outer join is run, it returns all the records from the second (right) table and the corresponding records from the first table that have a matching value in the join field of the second table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records or regular patients, then your SQL statement should resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E RIGHT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns 5 records as shown below.
Right outer join query result
The last returned record has empty data under the ID field because the record was from the regular_patients table and has no matching ID in the emergency_patients table. So Access returned an empty value in that field for that particular record.

FULL OUTER JOIN:

When a query with a full outer join is run, it shows all rows from both tables regardless of whether they are present in the second table. The SQL statement for a full outer join is database dependent. MS Access does not clearly support full outer join, but you can use a UNION query that combines the left and right outer join to perform it.

For example, still referring to the first two tables above, if you want your query to show the ID and HOME TOWN fields and return the records of all the patients of that clinic, that is both the emergency and regular patients, then your SQL statement would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID]
UNION
SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E RIGHT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID]
WHERE [E.ID] is NULL;

The query returns 7 records as shown below.
Full outer join query result

NOTE:

Do not use the ALL keyword when using a UNION query to perform a full outer join.

CROSS JOIN:

Cross Join is also referred to as Cartesian Join. It mostly returns undesired results. It is a join of tables or queries without specifying the join fields or the join condition. When this happens, MS Access combines every row from each table or query with the other rows of other tables or queries involved.

A cross join is rarely the desired result, but an indication that some join conditions are missing in the SQL statement.

An example of the SQL statement of a Cross Join between the first two tables above is:

SELECT E.[ID], R.[ID], R.[HOME TOWN]  FROM [emergency_patients] AS E JOIN [regular_patients] AS R;

Did you try running the above Cross Join SQL statement? What was your result?
Of course an error message!

Now let me test your knowledge in this chapter.

PRACTICAL EXERCISES ON SQL

Try to practise these SQL real life exercises on your own.
Study the two tables shown below. 
Customers table

Debtors table
Use them to answer and solve the following problem:

1. Design the two tables shown above inside one MS Access database.

2. With reference to these tables, write an SQL statement to select the ID field from the customers table and an aliased calculated field that holds the total amount owed by each of the debtors in each case. The SQL statement should contain a query to return only the records of each of the categories of people from the above tables:

a. Only those that are both customers and debtors.
b. All customers including debtors that are also customers.
c. All debtors including customers that are also debtors. 
d. All customers and all debtors.

This is the end of chapter 6 of this FREE online MS Access tutorial training series. 

Recommended MS Access Textbook



ms access textbook kindle format



Click Here to know more about the book.

In the next chapter (chapter 7), I will explain and illustrate how to create different types of reports in MS Access and then How to Design the Switch board.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

Share this

Related Posts

Previous
Next Post »

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!

Like Our Page Today