SELECT *
FROM #aaa a
WHERE EXISTS(select * from #aaa b where b.alertid = a.alertid and b.alerttypeid = 4)
AND EXISTS(select * from #aaa b where b.alertid = a.alertid and b.alerttypeid = 6)
AND NOT EXISTS(select * from #aaa b where b.alertid = a.alertid and b.alerttypeid not in(4,6))