relationship vss join

[Guest]

I'm not sure I understand the differance, if any, between relationship in
tables and joining fields in a query. It's making difficult for me to pull
data from different records.

 

[Tom Ellison]

Dear George:

A relationship and a join are concepts that have a lot in common, but are of
somewhat different meaning.

A relationship is designed when the database tables are envisioned and
created, and the referential integrity that enforces this relationship is
created.

A JOIN is a way of using such a relationship when creating a query. It is a
query mechanism that is typically the outgrowth of a designed relationship.

However, it is entirely possible to write a query that JOINs two sets of
rows even though no relationship has been designed or declared. Indeed, a
JOIN can be created on the relationship between columns even when the values
in the related columns between two sets of rows are not equal. This is
called a "non equi-join". It might be "less than" or "greater than" rather
than equal, or it might be joined on a function of the value in one set
being performed. For example, if one set contains:

A
B
C
D

having these values in separate rows of a specified column, and the other
set:

XAM
BCJ
AAZ
WMP

A join between these two sets on a function that tests whether the letter in
the first set is contained in the string for the second set would create the
following combination:

A XAM
A AAZ
B BCJ
C BCJ

Another relationship would be those where the letter in the first set is NOT
contained in the string in the second set:

A BCJ
A WMP
B XAM
B AAZ
B WMP
C XAM
C AAZ
C WMP
D XAM
D BCJ
D AAZ
D WMP

The above is a form of relationship, but not one that is likely "designed"
into the database initially, nor one on which referential integrity could be
applied. But it is a case where a relationship could be described, and on
which a JOIN could certainly be implemented.

In the process of designing a database, such non equi-relationships are not
given much thought. There's really nothing you are likely to want to do in
the design of the database to implement them. Most designers of databases
are not even thinking of such relationships in there data during the early
stages of creating the database, and indeed, they rarely come up later on.
But they are there.

Using the same data, a JOIN could be built where the values in the first set
are "less than" the values in the second, giving the results:

B AAZ
C AAZ
C BCJ
D AAZ
D BCJ

We could call this a relationship, but it would not be like the strictly
designed and enforced relationships that form relationships like one-to-one,
one-to-many, or many-to-many relationships. But it most definitely can be
expressed in a JOIN.

It is certainly not a simple thing to understand, with concepts like
functional methods and operators to describe what a relationship is.

Tom Ellison

 

[david epsom dot com dot au]

In Access, it is important to have indexes on the fields used in joins, and
really important to have a primary key index on a primary key field.

If you don't have these indexes, you get read-only queries
when you join the fields.

Indexes are automatically created for relationships and
for some field names. And of course primary key fields
are indexed.

Defining relationships is not necessarily critical for
you database, but defining indexes is, and defining
relationships is one way that people get indexes, sometimes
without even knowing why it is important.

(david)