Thanks for the response.

I tried the nested select to check the speed. I actually want to
delete all the transactions and their corresponding TransactionItems
and so this query was going to be

Delete from TransactionItems ........

That is why I considered a nested select as I could not delete
with a join!

-----------

I sort of follow your reason for repeatly performing the nested
query but it some how does not ring true. If the nested select is
repeated because of a back reference then it must be repeated for
every record in the TransactionItems table and the nested selected set
checked for compliance. This would be very slow!!

Cheers Des

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Which Firebird version are you using? If you use a recent version that support CTE's, you could try
>
> With T_CTE(TransactionNo) as (
> Select distinct TransactionNo from Transactions
> Where PersonNo = 200)
>
> Select TI.* from TransactionItems TI
> Join T_CTE T ON TI.TransactionNo = T.TransactionNo
>
> I actually stopped using IN <subselect> when Firebird was still named InterBase. At that time, EXISTS was by far superior. IN <subselect> has improved since then, but I've never had any need to use it. For a simple case like yours, it is easy to see that the subselect could be executed only once, but Firebird also has to handle cases like:
>
> Select * from TransactionItems TI
> where TI.TransactionNo in (Select TransactionNo from Transactions T
> where T.PersonNo = TI.PersonNo)
>
> where it is obvious that the subselect has to be evaluated for every potential row.
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of des.tuff
> Sent: 19. november 2009 14:14
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Using Nested Selects
>
> I found that selecting a set of Detail (TransactionItems) records using a nested select of the the Master (Transactions) Id (TransactionNo), was very slow. Why is this? I would have thought that
> it would have used the TransactionNo index to rapidly locate the few TransactionItem records associated with each Transaction.
>
> For example
>
> Select * from TransactionItems TI
> where TI.TransactionNo in (Select TransactionNo from Transactions
> where PersonNo = 200)
>
> TransactionNo is an indexed field of TransactionItems
>
> Is this the right way to approach this?
>