--sorry. i forgot an "and" in there
--somehting like this
SELECT DISTINCT
c.ContactID,
c.ContactName,
c.PhoneNum,
c.Fax,
c.PersonalEmailAddress,
c.CompanyPhoneNum,
c.GroupEmailAddress,
c.Address1,
c.Address2,
c.Address3,
c.Address4,
c.City,
c.State,
c.Zip,
c.CountryCode
FROM
tblDCF_SubAccounts s,
tblDCF_Contacts_Accounts a,
tblDCF_Contacts c
WHERE
a.ContactID = c.ContactID AND
a.ContactType = @ContactType AND
a.ContactSubType = @ContactSubType AND
s.HiNetMinor = a.HiNetMinor and
case when @ContactType = 'Broker'
then s.BrokerID
when @ContactType = 'FundAcct'
THEN s.FundAccountantID
when @ContactType = 'Custodian'
THEN s.CustodianID
else 0 end = @ID
Order By c.ContactName