IF statement in SQL to decide output?

[_sh]

Guys,

I have a SELECT statement like this...

SELECT (SELECT COUNT(ProfileID) FROM tbl_members_profile WHERE ProfileMemberID = MemberID) AS ExistProfile FROM tbl_members_main WHERE MemberID = 1

However, instead of the SQL returning a 1 or 0 for the ExistProfile column, I want to change it (at SQL point) into a 'yes' or a 'no'. How do I insert an IF/Case statement into SQL?

I've searched but no examples are making much sense to me!

[Lars_Berg]

select case when count(ProfileID) > 0 then 'Yes' else 'No' end AS ExistProfile 
  from tbl_members_profile 
 where ProfileMemberID = 1