I gave up on the first query - was 30 mins and counting! Massively appreciate your time Chris.

Ive tried another way albeit a copout from the IF EXISTS method - it seems to look like it works but could you please check it as well. It only took 2 minutes.

With TCM AS(

select distinct OTPTradeId from confirmationaudit where confoauditsenderref like 'HBEUM0%'

union

select distinct tradeid from confirmationsent cs, confirmationaudit ca where cs.confoid = ca.otptradeid

)

select TradeId, OTPTradeId As 'TCM ID',

case when OTPTradeId is NULL Then 'No' Else 'Yes' End as 'TCM'

from Trade left join TCM on TradeID = OTPTradeId

where tradedate = '17 Jun 2013'