A few great multiplayer titles, none of which requires GBAs:
Super Smash Brothers Melee (and the better you get the more fun it becomes)
Super Monkey Ball 2 (fun for everyone)
Alien Hominid (a classic but challenging two player game; has another, simpler four player mode which is also fun)
Pikmin 2 (mostly a great single player game, but the two player mode had me and my friend laughing our asses off, great fun)
I'm afraid I can't remember too many. I usually buy games with intense single player gameplay, my friend is often the one to buy multiplayer games.
You can play Zelda: Four Swords with a controller only in single player mode, but yes, in multiplayer every person playing needs a GBA and a connecting cord.