SQL Joins (Possible CASE or If Statement Needed) Help???

[habby0123]

I need to "tweak" the code below to account for item # 1 & 2 below. Can anyone point me in the right direction? Any help would be greatly appreciated!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--My Current Version of SQL for Outbound CNS to GML:
select  	t.CNSTransID, 
	t.DataSource, 
	t.UserID,
	CAST (t.ExternalFileDate as DATE) ExternalFileDate, 
        	t.ExternalFileIdentifier, 
	t.OrgSysTransID, 
	s.HiNetMajor, 
	t.HiNetMinor, 
	t.SettleCurrCode, 
        	t.SecurityID, 
	--SecurityIDType, ** need to build in join 'correctly' **       	p.RecordType, 
	t.TransCode, 
	CAST (t.TradeDate as DATE) TradeDate, 
	CAST (t.SettleDate as DATE) SettleDate, 
        	t.QuantityShares, 
	t.SettlePrice, 
	t.UnpaidOrders, 
	t.UserComments, 
	t.UserRemarksID, 
	--User Remarks, **need to get description **        	t.Market, 
	t.ExecBroker, 
	t.ClearingBroker, 
	t.LongShortInd,
	t.CustCode, 
	CAST (t.TaxDate as DATE) TaxDate, 
        	t.MktPrice,
	t.NetAmountLocal,
	t.TradedInterestIncCurr, 
	t.AutoSettleInd, 
	t.CommType, 
	CAST (t.EffDate as DATE) EffDate
from    tblDCF_Transactions t, tblDCF_TransactionTypes p, tblDCF_SubAccounts s
where   t.TransCode = p.TransType
and     t.HiNetMinor = s.HiNetMinor
and     t.UserID = 77107
and     t.ExternalFileDate = '12/16/2010'
and     t.ExternalFileIdentifier = '0630'

Need help with:
1) SecurityIDType - related to the SecurityID field, which isn't always set in tblDCF_Transactions. We need to scan the tblDCF_Securities table if it is populated and return the security type associated with the value stored within the SecurityID field (e.g. CUSIP, SEDOL, Ticker). Another trick with this field, though, is that if the TransCode field in tblDCF_Transactions is of Record Type T04 (SRI, SDO) - we need this value to return as "CURRENCY", even though they are not stored within the tblDCF_Securities table itself - they'd be instead stored within tblDCF_Currencies.

2) User Remarks - this also isn't always set on tblDCF_Transactions - but, when it is - we need to join this table and its UserRemarksID to the tblDCF_TransactionCannedRemarks table using the ID's to link, then return the TransDesc field from the tblDCF_TransactionCannedRemarks table.