I finally sused this out

select _BoxNo ,
(Select Count(_BoxNo) from emails where _Boxno = mailboxes._Boxno) AS _OnFile ,
(Select Count(_BoxNo) from emails where _ReadBy DOES NOT CONTAIN 'RL' AND _Boxno = mailboxes._Boxno) AS _Unread
from mailboxes

But the execution plan gives no indication wether its optimised or not. I'm pretty sure it is (ie there are indices on mailboxes._Boxno, emails._BoxNo & emails._ReadBy) but as I develop more complex ones how can I make sure?

Roy Lambert

Roy,

<< But the execution plan gives no indication wether its optimised or not.
I'm pretty sure it is (ie there are indices on mailboxes._Boxno,
emails._BoxNo & emails._ReadBy) but as I develop more complex ones how can I
make sure? >>

For now you have to test it with constant values outside of the scope of a
sub-query.

--
Tim Young
Elevate Software
www.elevatesoft.com