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?