A join may duplicate rows from the other tables. As example, if you have a
table, dices, with one field, diceValue, with 6 records, one record for each
value from 1 to 6, then:
SELECT a.*, b.*
FROM dices AS a, dices AS b
return 36 rows, one row for each possible match (we asked for nothing
special).
SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue >= b.diceValue
should return 21 rows, one row for each possible match ( we asked for the
first dice to be larger or equal, in value, to the second dice).
Using:
SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue = b.diceValue
will return only 6 rows, but if the table would have a duplicate value, say,
twice the value 6, we would have got 9 rows.
The logical result should be equivalent to the following procedure:
Take all possible combination between records from the two tables, then,
eliminate those not satisfying the ON clause.
So, with
Dices
-------
diceValue
------
1
2
3
3
Evaluation of
SELECT a.*, b.*
FROM dices AS a INNER JOIN dices AS b
ON a.diceValue = b.diceValue
is logically equivalent to :
step 1:
a.diceValue b.diceValue
1 1 'first record and first record
1 2 ' first and second
1 3 ' first and third
1 3 ' first and fourth
2 1 ' ...
2 2
2 3
2 3
3 1
3 2
3 3
3 3
3 1
3 2
3 3
3 3
step 2: evaluate the ON clause and keep only those where it evaluates to
true. In this case, that keeps 6 rows:
1 1
2 2
3 3
3 3
3 3
3 3
NOTE: the result of an INNER JOIN must be LOGICALLY equivalent to that but
that does NOT MEAN the PROCEDURE to get it is exactly like the one I just
used.
So, you should see, now, that if you have a problem of "multiplication" of
records, that is probably because there is a DUP common in both involved
tables, (or because you want a GROUP BY query, and forgot to use one). If I
would have just said this simple sentence, the reason why whould have been
nebulous, so, excuse that lengthily comment.
Vanderghast, Access MVP
armtwist said:
I am an access novice running a database. Why am I getting recurring names
for the following sequence? They should only occur once each.
SELECT Demographics.LAST, Demographics.FIRST, Demographics.DOB,
StudyIndex.DATE, [ICD Implant].[ICD Type], *
FROM [ICD Implant], Demographics INNER JOIN StudyIndex ON Demographics.MR
=
StudyIndex.MR
WHERE (((StudyIndex.DATE)>#1/1/2003# And (StudyIndex.DATE)<#12/31/2007#)
AND
(([ICD Implant].[ICD Type]) Like "*Dual*"));
