So why is it necessary to own 3 programs? Because HDR is relatively new, and sometimes one program handles the merge much better that the other two.
My current HDR lineup, in order of preference, is HDR Efex Pro, Photoshop CS 5 and Photomatix.
HDR Efex Pro
While all can produce outstanding results, HDR Efex Pro gives me the most control, the most options and the simplest workflow. As a result, I tend to reach for it first. And generally, that pays off.
Photoshop CS5
Photoshop CS5 is my fallback. While it doesn't give me all the options HDR Efex Pro does, it excels at image alignment. So, when I hit a series of images that HDR Efex Pro just can't seem to line up correctly, it's off to Photoshop.
It's not my go to program because of the extra work it requires to get what I want, and because Photoshop HDR seems tuned to produce kind of flat, naturalistic HDR images. HDR Efex Pro does that, and so much more, in an interface which works well for me. But since HDR Efex Pro still has some hiccups when it comes to image alignment, I still have to go with Photoshop's HDR from time-to-time.
This image is a perfect example. HDR Efex Pro just could not get the images aligned right, which made for an unacceptably fuzzy picture. Photoshop CS5 got the alignment correct, but produced a flat, uninspired image that needed significant work in Viveza 2 and Aperture :

Photomatix

Photomatix used to be my go to program, but now languishes in 3rd. It has its own look, and if I'm not getting what I want out of HDR Efex Pro or Photoshop I'll turn to it. But that's a rare thing these days, as generally I get what I want out of the first two.
A Matter of Taste
If you were looking for a definitive take on which HDR program is the only one you'll ever need - sorry. :-) Each has it's strengths and weaknesses and quirks, and how well those dovetail with your artistic vision and way of working will determine the one you like the most.