Ehm...

> 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

You're using aliassed tables, non-aliassed columns etc etc -
Perhaps this helps? :

SELECT comparearch.FID, owner1.FULLNAME as NAME1, comparearch.SUSPECT,
owner2.FULLNAME as NAME2
FROM
comparearch JOIN owner owner1 on (comparearch.FID = owner1.ASERIAL)
JOIN owner owner2 on (comparearch.SUSPECT = owner2.ASERIAL)
WHERE
comparearch.FID <> comparearch.SUSPECT

Can you tell us the result of that?

With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com