Sigh all you want, it does happen. Probably quite often, given that people get crushes regardless of inclination, either their own or the other person's. Is it really that different from having a crush on an opposite sex person and finding out that she's got a boyfriend? Unavailable is unavailable.
....
Indeed, like Picard and Beverly Crusher for example...