Recurring Names

[armtwist]

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*"));

 

[Michel Walsh]

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

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*"));

 

[KARL DEWEY]

You need a JOIN between [ICD Implant] and at least one of the other tables.
What is it related to?

 

[John W. Vinson]

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*"));

You have a "Cartesian Join" here: for each record in [ICD Implant] you'll see
every record in the query joining Demographics to StudyIndex, and vice versa.
How are the tables related? Should there be a join from [ICD Implant] to one
of the other tables?