Subject | multiple joins not returning correct information |
---|---|
Author | jsadlon2001 |
Post date | 2003-08-02T14:37:22Z |
Multiple Inner join problem
Givens:
Table COMPAREARCH has 1 record,
FID=9056, SUSPECT=9085
Table OWNR has 2 records,
ASERIAL=9056, FULLNAME=JOHNNY
9085 RON
SELECT
COMPAREARCH.FID,
OWNER.FULLNAME,
COMPAREARCH.SUSPECT,
OWNER1.FULLNAME AS FULLNAME1
FROM
COMPAREARCH
INNER JOIN OWNER ON (COMPAREARCH.FID = OWNER.ASERIAL)
INNER JOIN OWNER OWNER1 ON (COMPAREARCH.SUSPECT = OWNER1.ASERIAL)
WHERE
(FINGERID <> SUSPECT)
Given the above query:
The results are 9056 RON 9085 RON
Where the results should have been
9056 JOHNNY 9085 RON
Givens:
Table COMPAREARCH has 1 record,
FID=9056, SUSPECT=9085
Table OWNR has 2 records,
ASERIAL=9056, FULLNAME=JOHNNY
9085 RON
SELECT
COMPAREARCH.FID,
OWNER.FULLNAME,
COMPAREARCH.SUSPECT,
OWNER1.FULLNAME AS FULLNAME1
FROM
COMPAREARCH
INNER JOIN OWNER ON (COMPAREARCH.FID = OWNER.ASERIAL)
INNER JOIN OWNER OWNER1 ON (COMPAREARCH.SUSPECT = OWNER1.ASERIAL)
WHERE
(FINGERID <> SUSPECT)
Given the above query:
The results are 9056 RON 9085 RON
Where the results should have been
9056 JOHNNY 9085 RON