I'm going to admit, I myself have only ever played Halo 3, so there's probably a lot I don't know, but I'll say what I can from what I DO know.
I found the single-player on Halo 3 to be very, very non-stimulating. Co-op helps a bit, but not much; I never really got into the campaign and couldn't really be bothered playing past the first couple of levels.
I will say that the multiplayer is a blast with a bunch of people (Just make sure they're GOOD people, preferable a local multiplayer. I've heard very bad things about the general Halo 3 online community...), and I did have fun with that, but it feels like the game is a multiplayer with a tacked-on single player, and need to pay the price of a full game for... as well as an XBL Gold account subscription. (But that's another rant entirely. Let's just say I have my own opinions on needing the Gold account to play online, and I very much dislike it. That's more something against Microsoft than Halo 3 specifically, though. It's also why I'm mostly a PC gamer.)
Halo 3 isn't strictly a BAD game, but I have no idea how the series got such a massive fan-following. The weapon selection is alright, but the single-player campaign is short (and yet I still couldn't be bothered finishing it. I don't own the game myself, mind, I was playing it at someone else's.), and pretty much all the enjoyment in the game takes place online, which isn't in itself bad, but look at something like Call of Duty 4. It has an awesome multiplayer too, but I also really enjoyed the single-player campaign.
It might be a game I would consider getting at some point, but I'd also say that it's only something you should get if you REALLY can't find better. And I mean from any generation; go and look at older games to see if there are any gems you missed the first time around.
In fact, even after that, see if there are any awesome mods and get a good game for free out of one you already have before you get Halo 3, too.