> >SELECT FIELD1, FIELD2,
> >(SELECT FIRST 1 FIELD1 FROM DETAILTABLE WHERE
> DETAILTABLE.FK=MASTERTABLE.ID
> >ORDER BY SOMETHING) DETAILFIELD1 ,
> >(SELECT FIRST 1 FIELD2 FROM DETAILTABLE WHERE
> DETAILTABLE.FK=MASTERTABLE.ID
> >ORDER BY SOMETHING) DETAILFIELD2
> >FROM MASTERTABLE WHERE <mastertable row selection criteria>
>
> I had thought of this, as I had gotten sub-select for the first
> field to work, and have been using a second select to retrieve
> the remainder of the fields that I need from the detail record.
> However, my concern was for the execution plan: Is the firebird
> server optimiser smart enough to recognize that the second
> sub-select is getting the same record as the first, and just
> using the record it had already retrieved, or is it going to
> retrieve the record again? I ask because this example only
> retrieved two columns from the detail record, but the real-life
> code retrieves quite a few columns, and I worried about impacting
> the execution time of the select.
>
> For example, the execution plan for the example you provided
> looks like this:
>
> PLAN (D INDEX (MASTERID_INDEX))
> PLAN (D INDEX (MASTERID_INDEX))
> PLAN (M INDEX (MASTER_STOCK_INDEX))
>
> This made me think that it was indeed performing two searches for
> the detail record.
>
> Thanks,
>
> Dan.

It probably will - if you need a lot of fields from the detail table, use an
SP instead - you an use one select to retrieve them.. then again you won't
know the plan for an SP
Alan