SELECT
	m.MemberGUID,
	m.MemberName,
	CASE WHEN s.MemberAttribute = 'Disable' THEN 'Best' ELSE 'Regular' END AS MemberAttribute
FROM
	Member m
	OUTER APPLY
	(
		SELECT TOP (1) AttributeName
		FROM 
			Attribute a
			INNER JOIN MemberAttribute ma
				ON ma.attributeGuid = a.attributeGuid
		WHERE
			ma.MemberGuid = m.MemberGuid
		ORDER BY
			CASE WHEN AttributeName = 'Disable' THEN 0 ELSE 1 END
	) s