Not much in the way of details to work with here. I took a shot in the dark. See if this gets you close.

select * from

(

select ROW_NUMBER() over(partition by c.ClientID order by case when at.CODE = '004' then 1 else 0 end) as RowNum,

CASE

WHEN at.CODE = '004'

THEN a.FName + ' ' + isnull(a.MName, '') + ' ' + + a.lname

ELSE c.FName + ' ' + ISNULL(c.MName, '') + ' ' + c.LName

END AS Tag_Data

FROM Client AS C

LEFT JOIN

-- Note the order of the clausing here. It causes an

-- inner join to the rest of the tables BEFORE the

-- outer join is included.

Client_To_Alias_Collection AS AC

INNER JOIN Alias AS A

ON AC.OID_Link = A.OID

INNER JOIN Alias_Type AS AT

ON AT.OID = A.Alias_Type_Moniker

ON c.oid = ac.oid

AND A.EXPDate IS NULL WHERE c.OID = '12345'

) x

where x.RowNum = 1