<

INNER JOIN a Number Of Tables

Published on
16,281 Points
4,181 Views
6 Endorsements
Last Modified:
Awarded
Community Pick
INTRODUCTION

Joining tables is a way to tell the DBMS (Access in this article) what data to hold in memory from a group of tables. The purpose of a DBMS is to store, retrieve, and analyze data. Join is a way to limit the retrieved data that is stored in memory for further processing.  Joins include cross join, inner join and outer join types.

This article addresses the inner join and introduces a way to help design the FROM clause of a SQL statement using INNER JOIN of many tables. It removes the confusion of ordering and bracketing pairs of tables in the join sequence. This is treated with reference to the way a relation is drawn in query design mode.

This is a step by step tutorial using the Access environment.  A sample database is included to reduce the effort of recreating the necessary setup needed to continue with the tutorial.

A new approach will be presented to compile the join part by knowing the order of relations between tables, and the start and end fields of each relation

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.


PREREQUISITES

It is assumed that you:
understand  the difference between a Table and a query;
know how to use the query designer to relate tables;
understand how to set criteria in a query grid.


OBJECTIVES

For a database of 5 tables, A(a, r, x), B(a, b, r, x), C(b, c, r, x), D(c, d, r, x), and E(d, e, r, x), and a table of relations between these tables, the reader will be able to:
 
write the JOIN part of the SQL statement involving required tables
rewrite the JOIN part of the SQL statement when adding, removing, or changing direction of, a relation between tables.


NOTATIONS

In this article we will introduce few notations. These notations are not intended to be added to your store of notations, but to help in a concrete understanding within this article.  We introduce the expression XYZ, a three character name, with the following simple rules.
X can be any of the following,  group1, characters:  A, B, C, D, or E
Y can be any of the following, group2, character, a….z
? is a wild card that replaces any , group2, character, a….z
Z can take any of , group3, characters,  F, or T
The following are acceptable for the XYZ notation.
» A?F. Example AaF, AxF
» B?T. Example BaT, BxT
» Two notations linked graphically are shown as follows:
 
»» A?F   B?T. Example AaF BaT, a relation is drawn from A.a to B.a
»» B?F   A?T. Example BaF AaF, a relation is drawn from B.a to A.a  
 
» A?F   B?T, link is drawn from left to right.
» A?T   E?F, link is drawn from right to left
 

Table of relations

Figure1
The relations between the tables, as read from Figure 1 are as follows:
Single relation, and contributes to a simple criteria condition.
A?F  B?T, criteria A.? = B.?. Example. A.a =  B.a
B?F  C?T, criteria B.? = C.? . Example. B.a = A.a
C?F  D?T, criteria C.? = D.?. Example. C.a = D.a
Double relation, and contributes to a compound criteria condition.
D?F  E?T, and D?F  E?T, criteria (D.? = E.?) AND (D.? = E.?) Example. (D.a =E.a) AND (D.x = E.x)

DATABASE  SETUP

Figure2

A copy of the sample database is included, and includes the queries used in this article. See attached documents.
join01.accdb   join01.mdb

How to: INNER JOIN

Step 1: Open the database, and make sure you see the 5 tables. A, B, C, D, and E. as shown here.  Figure 3.

Figure3

Step 2: List tables, A and B, as shown bellow. Figure 4.

Figure4

Step 3: Design a query, which lists the content of tables A and B and show the design view, SQL view, and the result of running the query. Figure 5.

Figure5

This step produces the Cartesian product of tables A and B, where every record in table A is combined with every record in Table B. It is referred to as a cross join. The result shows 6 records, 2 records of A each combined with every row (3 times) in table B. The eligible records are records 1, 5, and 4, as they appear in Table 4. This is the way access pulls the records when the tables are used as such. The cross join result forms one entity stored in memory, where we can select the proper data using a criteria, or automatically by properly joining the tables, as will be seen next.

Step 4: Design a query to show the relevant records from tables A and B.  This is the same as the previous query, Step 3, but we add a criteria A.a = B.a. Figure 6.

Figure6
Step 5: Design a query to show the relevant records from tables A and B by INNER JOINing the two to each other. The same result will be as step 4. Inner Join is a way to link two tables relating two fields that set the criteria for selecting records. Figure 7.

Figure7
Step 6: Writing INNER JOIN SQL from relations table. Figure 8.

Figure8
The result of both queries will be the same, so you can assume any direction for the links and construct the query as shown. But be aware of that behavior when constructing the queries.
A INNER JOIN B is treated as one entity in memory, (AB) for short, this entity can be cascaded using INNER JOIN with a third table and so on. A bracket surrounds every two INNER JOINed entities. Hence nested brackets are used to join many tables.  Adding a third table C to the joined tables will result in a new entity C INNER JOIN (A INNER JOIN B), C(AB),  if the link end on C table is the starting point of the link, or (A INNER JOIN B) INNER JOIN C, (AB)C,  if the link end on the C table is the ending point. The result will be the same, but sometimes you cannot follow an ordered way of drawing links, you need to add, or delete tables and links in any order.

Steps 7, 8: Writing INNER JOIN SQL from relations table for 3 tables (entities). Figure 9.

Figure9
Reversing the second relation, involving table C has the effect of moving C to start of entities, followed by the compound entity formed from tables A and B, which will be shown inside a bracket to represent a single resultant entity.

Step 9: Writing INNER JOIN SQL from relations table for 5 tables (entities. General method). Figure10.

Figure10
Step 10: Writing INNER JOIN SQL from relations table for 4 tables, with last link is completing the join. Figure11.

 
Figure 11: This figure expands on last question happens if we miss one relation? by 
adding this relation at end of relations table.

|-----|-----|-----}-----|-----|
|     |  A  |  B  |  C  |  D  |
|-----|-----|-----|-----|-----|
|  1  |  aF | aT  |     |     |
|-----|-----|-----|-----|-----|
|  2  |     |     |  cF | cT  |
|-----|-----|-----|-----|-----|
|  3  |     | bF  |  bT |     |
|-----|-----|-----|-----|-----|

As stated before the 1st and 2nd links will yield two entities (AB), (CD).
The 3rd link INNER Joins the 2 enties producting the following SQL.

SELECT *
FROM (A INNER JOIN B ON A.a = B.a) INNER JOIN (C INNER JOIN D ON C.c = D.c) ON B.b = C.b;

Open in new window


CONCLUSION

We demonstrated a way to write the INNER JOIN part of a SQL statement for few tables. We start from first relation and that gives (from table) INNER JOIN (to table). The next link dictates the position of the 3rd table, if the relation is drawn from it, then it is INNER JOINed to the result of link1, otherwise the result of Link1 is INNER JOINed to it. We repeat the same process with other tables.

If a new  link is added that repeats both from and to sides of a link to tables previously processed,  they are not repeated in the INNER JOIN rather,  we only incorporate the fields in the join conditions.

The results of the joins are the same, if they appear the same in design view, although the arrangement of tables looks different. Following the approach presented simplifies the writing and the debugging of such queries.

If a link is out of order, meaning missing one relation, this will produce a cross join of two entities. And if the last missing link is introduced, the SQL will INNER JOIN the two entities.

Note: Hope this article reduces your confusion about INNER JOIN. If you find this useful, please select was helpful, when asked.
   
6
Comment
Author:hnasr
  • 3
  • 3
7 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
Nice work, here, you have my Yes vote above!
0
 

Expert Comment

by:funaroma
Consider using real-world examples in articles like this.  Using tables and columns and data consisting of just letters and numbers limits the article's intelligibility. Technically correct, but difficult for new users to read and understand.  The more we encourage use of actual SQL to present table schema, and the use of real-world scenarios to present problem-solving techniques, the more useful the expert knowledge becomes -- people (especially Access users!!!) just don't think in terms this abstract; you need to model your example the same way these folks are (hopefully) modeling their design -- after the Real World.  Just a little advice - not at all unappreciative of the effort!
0
 
LVL 31

Author Comment

by:hnasr
funaroma,

I appreciate  your comment.

The use of abstract names for tables was a decision I have to take because of the diversity of users interests and likes. A real-world example for one may be incomprehensible by another.

I invite you to add a real-world example that you like, so other readers of the article may check!

Apart from the names, didn't you find it useful? A works' value depends on what it adds to a person's previous knowledge before exposure to the work.
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 

Expert Comment

by:funaroma
If you feel your target audience can sift through all the letters and figures as they are in abstract form, then by all means, leave it.  It's WAY more likely that you're going to have success getting the ideas out to many more people if they are based in something more concrete.

As far as finding it useful... I already have a solid understanding of what INNER JOINs do, and was merely visiting to see your "take" on it, specifically with the number of tables you were joining.  There was really nothing new here for me, and drilling through the very abstract example was actually pretty tedious -- enough for me to take the time to comment.

If you're going to be a technical writer, the number one skill is to present things in incremental format using the most concrete examples possible.  Unless your target audience is used to thinking in the most abstract terms... I'm willing to bet that someone unfamiliar with these concepts is turning around and trying to make your examples more concrete anyway, so my advice was simply to do that for them, since you already (presumably) have a solid grasp of the subject matter.

The most successful technical books I've read, ever, regardless of subject complexity, were the Head First books.  They even tackled Design Patterns in that series, and they did an amazing job.  Read the preface of one of the Head First books - it's practically a biblical statement for how to teach the human mind to grasp something new, that's already abstract.

Just my own 2c, take it for what it's worth!
0
 
LVL 31

Author Comment

by:hnasr
funaroma,

" I already have a solid understanding of what INNER JOINs do"

That is fine, so giving the readers an example will not be difficult or time consuming.

A side note:
"something more concrete"
As an instructor, concrete does not mean using Employees instead of E. It means using more senses in understanding the subject.

This is important because I am trying to write an article ".... from concrete to abstract".

Left Join is an abstract notation of a more elaborate understanding with examples and practice of how to display information from tables. Same applies to Cartesian Product.

But our use of A for a table is an abstract, because it hides unnecessary and distracting issues.
0
 

Expert Comment

by:funaroma
"That is fine, so giving the readers an example will not be difficult or time consuming."

When did this become MY job?  You're the "expert." who wants votes.  =)  I simply proposed that a real world example would reach more people.  I stand by that statement.

"As an instructor, concrete does not mean using Employees instead of E."

Depends on what, and whom, you are instructing.  You haven't mentioned who you feel your target demographic for this article actually is.  As an instructor, that's the first thing you should understand.  If you were to come back to me and tell me "this article is intended for an audience of Computer Science majors, at least 2nd or third year" then I'd not have an issue.  Beyond that, I think your article is difficult to approach for the average Access user.

"This is important because I am trying to write an article ".... from concrete to abstract"."

This would be great if it were true.  But you couldn't have started out any more abstract:

"For a database of 5 tables, A(a, r, x), B(a, b, r, x), C(b, c, r, x), D(c, d, r, x), and E(d, e, r, x), and a table of relations between these tables"

Notations do NOT lead to more concrete understanding.  Quite the contrary - they are just another layer of abstraction.

I've met a lot of people, right out of college, that studied with instructors "teaching" exactly this way.  Frankly, their problem solving skills left a lot to be desired, and they couldn't code (or troubleshoot, or even convey meaning) their way out of a paper bag.  If they can even remember which concept to apply (because they never had a concrete, real-world example to which their minds could attach the exercise or technique), they often apply it completely incorrectly.

This was not intended to be a pissing match.  Heck - someone already voted "yes" to your article long before I came along, and as of this comment, 5 of 6 people found it "useful."  So it looks more and more likely that I'm completely off base here.  I feel the suggestion I've made couldn't HURT you any...  I think you'll get your (valuable!!) message across to a lot more people, with a broader range of experience and needs, if you consider updating and changing your writing style for this or for future articles for this site.  This site is not a CS400 college text book, it's a site where people with much less skill and knowledge come to get help.  Writing for that audience is a skill all its own, and would highly compliment the skills you clearly already possess.  It's HARD to make things look easy... but the challenge is worth it.  If more college professors understood this, many more college students would graduate with more useful skills, right from the start.

Take it for what it's worth... I have no personal bone to pick with you of course.  It looks like I might be the odd man out on this one, but give the suggestion some thought anyway... perhaps just consider it an exercise for your OWN development as a writer.  I'd be interested to see what you came up with.
0
 
LVL 31

Author Comment

by:hnasr
funaroma:

My last comment on this side argument.

"When did this become MY job? '
I was expecting a real example of the type you think a user can better learn from.


"You haven't mentioned who you feel your target demographic for this article actually is"

In the second subheading in the article:

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.
0

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month