Claudio,

Thanks for the reply.

> This is a LEFT [OUTER] JOIN. The JOIN/ON syntax is the recommended way to
> handle things. There's no way you can produce an OUTER JOIN in the WHERE
> clause in IB with one condition, because it adheres to the ANSI-SQL way of
> doing things. In MsSql, you can use non-ANSI, proprietary *= and =*
> operators in the WHERE clause to do the same. However, in MsSql v7, MS
> itself dumped them in favor of the JOIN syntax.

I realized after sending the message that these 2 statements did not return
the exact same results.

> The short answer is YOU CAN'T. When IB is given an [INNER] JOIN, it builds
> PLAN JOIN (t1 ORDER index1,t2 INDEX (index2))
> but when it sees a LEFT [OUTER] JOIN, it builds
> PLAN SORT (JOIN (t1 NATURAL,B INDEX (index2)))
> and there's no way to tell it to use index1 for t1. If you try to force

the

> supposed good plan, IB refuses it.

Thank you. I have been trying to get an ordered LEFT JOIN for several hours
now and it has been driving me crazy. I wanted to use the LEFT JOIN syntax
to consolidate several TIB_Cursor components into one, therby reducing the
overall network traffic

Any idea why an ordered LEFT JOIN is not supported ? This certianly seems
like it would be useful ?

> You will have to think in another solution because your left join as it
> seems to use all fields from both tables, cannot be decomposed in a main
> select plus an inner select (subquery) that returns just one field. So,
> performance cannot be enhanced easily.

I really do not need all fields from the second table and only presented the
example this way to simplify it.

I have been playing with singleton inner select subqueries and was wondering
if there are any good guidelines to follow for their usage. Specifically, if
I need say 5-10 fields out of the second table, am I better of using a
separate TIB_Cursor to retrieve them or is the inner select acceptable ?

Thanks for the insight,

Chris Landowski
Dynamic Software Solutions