Martijn Tonies,

Brillant. SQL now returns the correct results.

Thank you

Johnny


--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

> 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