I have many of the mentioned video cards in this thread and have done extensive temperature tests on pretty much all of them (it was a hardcore geeky hobby of mine a few years ago and still is to a point.)
My GeForce 7300 GT and 7600 GS cards are the best, by far, when it comes to decently low temperatures and decent-to-very-good performance. Both of these will be about on-par with Radeon 9700/9800 non-pro... but with better support for DX9 games. In fact, both of them will also regularly equal or beat my GF 6800 XT and 6600 GT in DX9c game performance, and at half or less the power draw of the 6800XT. That being said, neither my 7300 GT nor my 7600 GS use the stock coolers. However, they can still run decently cool with single-slot coolers, provided those are bigger than the stock ones. In the case of my 7300 GT, it's from BFG and came with an anemic "U"-shaped cooler equipped with a tiny 50 mm fan screaming at full blast on 12V. It was loud and didn't run very cool... though not too bad either compared to many other cards I have seen: about 57-60C under gaming / 3D load with 19C room / ambient temperature (open computer case.) Of course, I wasn't happy with this result, since my room where I used to do all of these tests would regularly hit 28-30C in the summer... meaning, temperatures on older GPUs with no fan control would also rise by the same amount. So the same 7300 GT would get close to 70C in the summer, which is not good for it, considering it's a bumpgate -era card (keep these under 60C at all times if you want them to last.) As such, I gutted the original BFG cooler and put on a cooler from an XFX GeForce 8600 GT. While at it, I also wired the fan of the 8600 GT cooler to run at 7V to make it more quiet - it became almost inaudible, in fact. With this setup, my 7300 GT ran at 48C tops with the same 3D load and same room temperature - a reduction of 12C (while also being more quiet.) Not only that, but even when summer temperatures hit 28C, my GPU core never went above 58C. Most of the time, it stayed in the 55-57C range in 3D load. My PNY GF7600 GS performed in a very similar way... and perhaps this shouldn't be surprising, as it is rated for only 5 Watts more TDP (the 7300GT is 30W TDP... IIRC... and the 7600 GS is 35W TDP.)
Another decently cool mid-range video card is the GeForce 6200 / 6600 LE (NV43 core, 128-bit mem. bus DDR2). It has about the same TDP as the 7300 GT and 7600 GS (~30W) and you can almost always unlock 4 extra pixel pipes without any noticeable increase in TDP. It does tend to struggle a bit more in DX9c mode compared to the above two, particularly when I tested it with Portal or other Source engine -based games. Bumping down the DX_level to 8.1 or 9.0 did bring the FPS right back up, though.
Then there's the Radeon 9600 NP (non-pro) and 9550 128-bit: again, both of these being mid-range cards but with nice low TDP. I don't know if I can suggest going below this card, though. For example, I also have an HD2400 Pro. While it's a very nice and cool card and will run most games about on par with the Radeon 9600... that's only so in 1024x768 resolution... and certainly 3D scenes can make the 2400 Pro really crawl due to its 64-bit mem. bus.
As for higher-end cards... I don't know if there are any that are both cool-running and use a single-slot cooler. Usually it's pick between hot-running and single-slot cooler or cool(er)-running and dual-slot cooler... and in some cases neither (dual-slot cooler and the card still runs hot.) I think the closest to this would be an X800 GTO - at least based on tests from the one I have. It seems to run relatively cool according to its built-in temperature monitor and should beat the 7300 GT and 7600 GS in some games. However, I haven't tested it too extensively yet, so can't confirm if that's 100% true. The Realto bridge chip on the back of these newer Radeon cards surely does run hot - something not to be overlooked (additional cooling on top of it highly recommended for longer-term reliability.)
As for the GeForce 7800 GT and 7900 GS - I only have the PCI-E versions of these cards. The 7900 GS & GT both have the same TDP - about 50W... which isn't that much. Still, cooling that with a single slot cooler and a tiny fan is not possible... yet this is how all of these usually come stock. The 7800 GT is worse, because it's made on older fab node and thus is more power-hungry (about 60-70W TDP, IIRC.) And 6800 XT/vanilla/GT is even worse (60-80W TDP.) HD3850 is nice... but certainly not cool-running in the single-slot versions (i.e. those Sapphire cards.) If you're after that kind of performance, however, only HD4670 will be about on par. I personally like the HD4670/4650 series quite a bit. With the right single-slot cooler, an HD4650 (or HD4670) can be made to run relatively cool. While I do agree with others here that underclocking by itself doesn't generally yield lower temperatures (usually), I have found that for the HD4670, it does make a difference - I can shave 3-4C off of my HD4670 cards if I underclock them to run at the same core clock speeds as my HD4650 cards.
Anyways, that's all the cards I felt like digging up info for from my test results. Hope it helps.