Relationships and Joins

[Emma via AccessMonster.com]

Hi

I'm used to the concept of creating relationships between tables, generally
one to many; then whenever I have created queries the necessary relationships
have been there, or I have just been able to link the primary and foreign key
fields. After moving jobs I have recently come across the need to create the
relationships between the tables, when creating the queries, which has not
been too much of a problem, although I am a little confused as to whether I
need to relate each individual table with every other table where the
primary/foreign keys exist. My main concern is that I now know that I will
need to to change the join properties from property 1 to property 2 or 3 in
order for some of my queries to work successfully. I have never used this
functionality before and I'm not sure that I completely understand how it
works. My main concern is that I'm not entirely sure when I need to use them
and when a straightforward join is sufficient (a lack of understanding I'm
afraid!) Could anyone recommend a good book or website that could talk me
through this and perhaps given some demonstrations or practical exercises, so
I can familiarise myself!

Thank you in advance
Emma

 

[Jeff Boyce]

Emma

The thing about relationships is that they exist independent of any queries.
To get the best use of Access' features and functions, your data needs to be
well-normalized. One way to look at this is that each table reflects only
one 'topic', and only tables that actually have some relationship to each
other can be related (using primary/foreign keys).

In my world, tblVehicle (with vehicle VIN#, Model, ...) and tblAuthor (with
AuthorFirstName, ...) have NO relationship. Only you can tell if the tables
in your situation have a relationship. If they do, how can you tell which
records *( in one table) "belong" to which records (in the other table).
That's the use for primary/foreign key.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

After moving jobs I have recently come across the need to create the
relationships between the tables, when creating the queries, which has not
been too much of a problem, although I am a little confused as to whether I
need to relate each individual table with every other table where the
primary/foreign keys exist. My main concern is that I now know that I will
need to to change the join properties from property 1 to property 2 or 3 in
order for some of my queries to work successfully.

A Relationship serves two purposes:

- it enforces referential integrity, so you can't enter "orphan" records
- it provides a *common but not obligatory* default join when you create a
query.

In other words, you do NOT need to have multiple relationships defined just in
order to create different types of queries. You can create a query with no
join at all, or you can delete the default join inherited from a relationship
and create a new join, if that's needed for the purposes of the query.

John W. Vinson [MVP]

 

[Dale Fye]

Emma, the difference between the Type 1, 2, and 3 queryies has to do with
what you want your query to return. Lets suppose that you have a table of
employees and a table of sales (I would not have a TotalSale column in my
sales table but I'll put it here for ease of explanation) that contains an
EmployeeID to ensure that you can assess each employees sales success.

If you want a list of all the employees that everh made a sale, then you
would write a query that looks like:

SELECT E.EmployeeID, E.LastName, E.FirstName, SUM(S.TotalSale) as
EmployeeSales
FROM tblEmployees E INNER JOIN tblSales S
ON T.EmployeeID = S.EmployeeID

This query will only give you employees that made sales, but what if you
wanted a list of all your employees, and the amount of sales they had made.
That query would be what you are calling a Type 2 join and would look like:

SELECT E.EmployeeID, E.LastName, E.FirstName, SUM(S.TotalSale) as
EmployeeSales
FROM tblEmployees E LEFT JOIN tblSales S
ON T.EmployeeID = S.EmployeeID

The difference between Type2 and Type3 joins is really about the direction
the arrow is pointing in the join, and will determine whether the syntax
above indicates LEFT JOIN or RIGHT JOIN. The key here is that the table
that has the end of the line without the arrowhead will return all of the
recods from that table, regardless of whether there are matching records in
the other table. In example #1 above, your administrative employees who
have no sales would not show up. In example #2, they would show up, with a
0 in the EmployeeSales column.

HTH
Dale

 

[Chris2]

Hi

I'm used to the concept of creating relationships between tables, generally
one to many; then whenever I have created queries the necessary relationships
have been there, or I have just been able to link the primary and foreign key
fields. After moving jobs I have recently come across the need to create the
relationships between the tables, when creating the queries, which has not
been too much of a problem, although I am a little confused as to whether I
need to relate each individual table with every other table where the
primary/foreign keys exist. My main concern is that I now know that I will
need to to change the join properties from property 1 to property 2 or 3 in
order for some of my queries to work successfully. I have never used this
functionality before and I'm not sure that I completely understand how it
works. My main concern is that I'm not entirely sure when I need to use them
and when a straightforward join is sufficient (a lack of understanding I'm
afraid!) Could anyone recommend a good book or website that could talk me
through this and perhaps given some demonstrations or practical exercises, so
I can familiarise myself!

Thank you in advance
Emma

Emma,

Here's my explanation.

MS Access uses the relationships you create in the relationship's window when you go to
make a query.

When a query is created, and two tables are added to the query, MS Access looks at the
relationship that already exists between those two tables that were just added to the
query. It then uses the join-type that is noted in that relationship between the two
tables as the **default** join-type for the new query.

Relationships
Join Type = SQL Join Name

1 = Inner Join (Default for all new relationships.)

2 = Left Join

3 = Right Join

You can specify other default choices for join-types if you want on any relationship you
create.

A change to the default choice in the relationship affects only new queries that are
created after the change.

You can change the default join type that is given to you (from the existing relationship)
in any new query that you create.

You can change this by using the Query Grid to change the Join Properties by
right-clicking on the line between the tables in the Query Grid. In SQL View, you just
use a different join name in the SQL code.


What's the difference with Inner, Left, and Right?


Whenever you run a Query (SELECT, UPDATE, INSERT, DELETE, SELECT...INTO,
TRANSFORM...PIVOT) with more than two tables, you must instruct the database on what
columns are used to "join" the tables together.

INNER JOIN means that rows in both tables are returned where values match in both sets of
columns (one set from each table) that are used in the "join".

LEFT JOIN means that all rows in the left-hand table are returned, and when a row in the
right-hand table has no match for the specified "join" conditions, NULLS are returned in
any output columns of the right-hand table.

RIGHT JOIN means that all rows in the right-hand table are returned, and when a row in the
left-hand table has no match for the specified "join" conditions, NULLS are returned in
any output columns for the left-hand table.

"Left" and "right" refer to SQL code. It means to the left and right of the join keyword
of the SELECT statement (INNER JOIN, LEFT JOIN, RIGHT JOIN are all join keywords).


INNER JOIN Example:

SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
INNER JOIN
RightHandTable AS R1
ON L1.Key = R1.Key

Rows in both tables are returned every time L1.Key and R1.Key values match. No other rows
are returned.


LEFT JOIN Example:

SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
LEFT JOIN
RightHandTable AS R1
ON L1.Key = R1.Key

All rows in LeftHandTable are returned. Any time there is no R1.Key value for an L1.Key
value, R1.Key is Null.

Left/Right-ness can better be demonstrated if written as shown below, but due to reasons
of horizontal crowding, usually isn't.

FROM LeftHandTable AS L1 LEFT JOIN RightHandTable AS R1
ON L1.Key = R1.Key

LeftHandTable is effectively to the "left" of the LEFT JOIN keyword.


RIGHT JOIN Example:

SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
RIGHT JOIN
RightHandTable AS R1
ON L1.Key = R1.Key

All rows in RightHandTable are returned. Any time there is no L1.Key value for an R1.Key
value, L1.Key is Null.


CARTESIAN JOIN:

If you leave these instructions (join names) out of a query, the database will join every
row in the first table with every row in the second table. The number of output rows
equals the number of rows in the first table multiplied by the number of rows in the
second table. This is called a Cartesian Join (the results are called a Cartesian
Product), and is not used except for very specific queries.

For two large tables, say with 100,000 rows each, the output would be 10,000,000,000 rows.


EXAMPLES IN PRACTICE:

Two example tables:

Items:
ItemID -- Primary Key
ItemName

ItemID, ItemName
1, Popcorn
2, Chips
3, Soda
4, Fillet Mignon

Prices:
PriceID -- Primary Key
ItemID
ItemPrice
StartDate
EndDate

PriceID, ItemID, ItemPrice, StartDate, EndDate
1, 1, 1.00, 06/01/2007, 06/08/2007
2, 2, 3.50, 06/01/2007, 06/08/2007
3, 3, 1.00, 06/01/2007, 06/08/2007

INNER JOIN Example:

SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
INNER JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID

Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00

Notice that Fillet Mignon does not appear. There is no = match (after the ON clause) for
ItemID between the two tables.


LEFT JOIN Example:

SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
LEFT JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID

Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00
Fillet Mingon, Null

Fillet Mignon does appear now, even though there is no match on ItemID between the two
tables. The output column (ItemPrice) from the "right hand" table (Prices) leaves a Null
behind.


Notes:

The above joins are conducted on table primary keys. Joins can be done on any column.
Usually they are done on columns that represent the same types of information, like
numbers, dates, words, etc. With conversion functions like CInt() and CStr(), numbers and
words can be compared in a join. Usuing functions in a join is generally not a first
choice as it will usually stop the use of any indexes, and this can slow down many
queries.

Joins are usually done on primary keys, or between primary keys and foreign keys, because
these columns represent either the final way of identifying the table rows, or are the
natural information connections between the tables.


Extras:

DDL SQL (These queries are usable to create the two tables above so you can run the last
two queries above on your own. You copy and paste these, one each, into the SQL View of
an MS Access Query, and then execute the query. You will need to manually enter in the
sample data noted above into the tables created this way.)

CREATE TABLE Items
(ItemID AUTOINCREMENT
,ItemName TEXT(255) NOT NULL
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)

CREATE TABLE Prices
(PriceID AUTOINCREMENT
,ItemID INTEGER NOT NULL
,ItemPrice CURRENCY NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,CONSTRAINT pk_Prices
PRIMARY KEY (PriceID)
,CONSTRAINT fk_Prices_Items
FOREIGN KEY (ItemID)
REFERENCES Items (ItemID)
,CONSTRAINT un_Prices_ItemId_ItemPrice_StartDate
UNIQUE (ItemID
,ItemPrice
,StartDate)
)


Sincerely,

Chris O.

 

[Emlou85 via AccessMonster.com]

Thank you for all your help and explanations. I am particularly greatful for
the explanations regarding the joins aspect - it is making more sense now!
Thank you! Emma

 

[Chris2]

Thank you for all your help and explanations. I am particularly greatful for
the explanations regarding the joins aspect - it is making more sense now!
Thank you! Emma

Emma,

You are welcome!


Sincerely,

Chris O.