Hi Robert,

could you try..

SELECT a,b,c
FROM Table1 t1
JOIN Table2 t2 ON (t2.pk = t1.FK and t2.CancelFlag <> 'T')
WHERE t1.PK = 4

>Hi All
>
>I have a Query that is basically
>
>SELECT a,b,c
>FROM Table1 t1
>JOIN Table2 t2 t2 ON t2.pk = t1.FK
>WHERE t1.PK = 4
>AND t2.CancelFlag <> 'T'

"JOIN Table2 t2 t2" ???

Ali


--- In firebird-support@yahoogroups.com, Robert martin <rob@c...>
wrote:

> Hi Helen
>
> Thanks for your reed back.
>
> I realized the * was not so hot, the real query specifies all

fields and

> actually contains a couple of other tables. The query shown below

more

> simply demonstrates the problem. Your suggestion re using the =

is a

> good one however I had already tested it to no avail.

Interestingly

> adding the sl.* actually makes the SQL about 100ms slower !
>
> My further testing of other where clauses that would return the

correct

> 208 records indicates that all are equally slow. The second I

flip the

> SQL around it runs fast, but of course returns the wrong results!
>
> for example your query
>
> SELECT sl.* /* notice the identifier! */
> FROM SupplyLine sl
> JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
> WHERE so.EntityRef = 4
> AND sl.SupTranRef IS NULL
> AND sl.PackSlpRef IS NULL
> AND (sl.CancelFlag = 'F' or sl.CancelFlag is null)
>
> Returns 208 records and takes 1.568ms
>
> The same query with the F changed to T
>
> SELECT sl.* /* notice the identifier! */
> FROM SupplyLine sl
> JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
> WHERE so.EntityRef = 4
> AND sl.SupTranRef IS NULL
> AND sl.PackSlpRef IS NULL
> AND (sl.CancelFlag = 'T' or sl.CancelFlag is null)
>
> returns 334 records and takes 0.125s (note it takes 0.079s with

just * instead of sl.*).

>
> This is all very strange :)
>
>
>
>
> >
> >