Yes, this does work. We were rather hoping that it would work as is i.e.
if postgresql and SQL Server can do it, why can't Firebird? Is there a
problem with the optimiser.

Cheers

Svein Erling wrote:

> Hi Bradley,
>
> > InvHead.InvHeadRef is unique and has an index
> > AcctName.InvHeadRef is unique and has an index
> > IDRelate has an index on IDREf, capacity which is nearly unique.
>
> These are good indexes for this query
>
> > InvHead.IDRef has an index with a selectivity of about 2
>
> Whoops, this one is probably the reason why Firebird thinks a natural
> is the best it can do. Try changing your query to
>
> select ...
> from
> InvHead
> inner join IDRelate on
> idrelate.IDRef = (InvHEad.IDRef + 0)
> and IDRelate.Capacity = 'ADVISOR'
> left outer join acctname on
> invhead.invheadref = acctname.invheadref
> where invhead.invheadref = 'IH00025809'
>
> or similar to prevent Firebird from using this index. Report back to
> this list if it works or not.
>
> HTH,
> Set
> - I support Firebird, I am a FirebirdSQL Foundation member.
> - Join today at http://www.firebirdsql.org/ff/foundation
>
>
>
> ------------------------------------------------------------------------
> *Yahoo! Groups Links*
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>