George,
you're hiding things from us.

There's no way you can get a plan like

>PLAN JOIN (JOIN (A INDEX
>(RDB$PRIMARY243,RDB$FOREIGN254),B INDEX
>(RDB$PRIMARY9)),C INDEX (RDB$PRIMARY9))

unless you have a where clause (it has to use natural for TALBEA). My guess
is that your problem in some way is related to wrong usage of the where
clause, at least I cannot see anything in your select that should make your
first statement fail to produce a result if the second does.

Set

George Boutwell wrote:

>I have the following query:
>
>SELECT
> A.PK,
> A.OTHERFIELD
> A.FK1
> B.FIELD1
> A.FK2
> C.FIELD1
> A.FK3
> D.FIELD1
> D.FIELD2
> D.FIELD3
> A.FK4
> E.FIELD1
> E.FIELD2
> E.FIELD3
> A.ANOTHERFIELD
>FROM
> TALBEA A LEFT JOIN TABLEB B ON (A.FK1 = B.PK)
> LEFT JOIN TABLEB C ON (A.FK2 = C.PK)
> LEFT JOIN TABLEC D ON (A.FK3 = D.PK)
> LEFT JOIN TABLEC E ON (A.FK4 = E.PK)
>
>However, it returns back an empty result set (which as I understand
>it is wrong.) The plan for this query is:
>
>PLAN JOIN (JOIN (JOIN (JOIN (JOIN (A NATURAL,B INDEX
>(RDB$PRIMARY9)),C INDEX (RDB$PRIMARY9))),D INDEX
>(RDB$PRIMARY22)),E INDEX (RDB$PRIMARY22))
>
>If, I trim the first query down, taking out the references to TABLEC
>SELECT
> A.PK,
> A.OTHERFIELD
> A.FK1
> B.FIELD1
> A.FK2
> C.FIELD1
> A.FK3
> A.FK4
> A.ANOTHERFIELD
>FROM
> TALBEA A LEFT JOIN TABLEB B ON (A.FK1 = B.PK)
> LEFT JOIN TABLEB C ON (A.FK2 = C.PK)
>
>I get the resultset I expect, except that now I don't have those fields
>that I would really like to have added to the query. The plan for this
>query is:
>
>PLAN JOIN (JOIN (A INDEX
>(RDB$PRIMARY243,RDB$FOREIGN254),B INDEX
>(RDB$PRIMARY9)),C INDEX (RDB$PRIMARY9))
>
>How can I join these plans to return the correct result set for the
>first query (or even better re-write the first query to return the
>correct result)?