Metroid Prime trilogy, period. Dark Samus is effin' badass.
Other games that have a great story: Valkyria Chronicles, Metal Gear Solid (series as a whole), Shadow of the Colossus, Mass Effect (both, waiting patiently for the third one), Half-Life 2, Final Fantasy VI and VII, God of War series (best “what if” of the Greek mythology, EVER), and much, much more
dude we have very similar taste haha. also fallout 🙂 the story itself i guess is lacking but with all the running around like a chicken with its head cut off in the game you learn a lot of storylines. i love fallout and borderlands both <3