Foam/Lead etc works well but not as well as easier to apply and thinner products.
You can buy sound proofing sheets from computer suppliers (some of them), I noticed performance-pcs.com (I think that's the site) sold them. It's basically stick on rubber thats designed to reduce resonance and block noise.
I find that reducing the number of fans and using the quietest fans to be a good start, also using an SSD drive will cut noise. Even at their lowest speed rating, most fans contribute a lot to noise, so the less fans the better (with regards to noise).
Watercooling will be good, but with a sound deadened PC case, it may be quieter to have a single 120mm PC fan inside the case, than a pair of 120's running slowly outside the case for example. Might be worth thinking about.
You may even be able (if you dont want to overclock) get away with a huge heatsink that can be run passively (no fan).
Edit: reply to dynamat quote above, I did car audio previously, and Dynamat Extreme is definately THE best sound deadener product I ever found. I was just unsure of how its reflective nature would impact a PC. In a car you want it to reflect the sound inside, but absorb resonance in the panel, and block outside noise. In a computer you want it to absorb the internal noise, I'm not 100% sure on the topic, I have some DMX and spare computers at home, I might do some experimenting.
I am PLANING on getting water cooling alot of my Hd's ,and other components,yet Im going to over clock with cpu fan maybe. Im building my case so that i can have an super powerful fan blowing directly onto the mother board