I mean for me it was a Wii game (Iknow it started as a game cube game but I had it on the Wii), but that kind of means there's only one Zelda game that matches your description/falls in your perametters and that's Skyward Sword, and one game really doesn't make a trend.

Its not one game. Mario 3d world is smaller and simpler than mario 64, sunshine. Recent nintendo shows, they are going for smaller budgets, simpler games. I still think they're excellent games, I just dont think they're epic anymore.

That's not true at all. The only time Nintendo never pushed the limits of their system was with a lot of the later GameCube games. Their NES games visually improved a lot over the course of five years. From the beginning, some of their SNES games used Mode 7 graphics and some of their later games such as Super Metroid and Yoshi's Island pushed the SNES. From the beginning of the N64, Mario 64 was a technical masterpiece (just compare it to any other 3D platformer of the time) and Wave Race 64 was by Shigeru Miyamoto's account one of the most technically sophisticated N64 games ever created. The physics and techniques required to create the water in that game were insane for that time nobody could do that. Other N64 games like Star Fox 64, 1080 Snowboarding, and both Zelda games were constantly pushing the N64's limits. Early GameCube games like Luigi's Mansion, Pikmin, and Smash Bros. Melee did a good job demonstrating the GameCube's capabilities and even a few later games like the Metroid Prime games also did the same thing. With the Wii, that system was almost the same as the GameCube, yet Smash Bros. Brawl and the Mario Galaxy games looked like they were half a generation ahead of their predecessors. 

Mario Galaxy was a more ambitious game with higher production values than Sunshine. (Full orchestra, epic setting and cutscenes, bold new concept)

Im actually interested to see how Zelda U will look. I think it will look good, maybe it will convince me to pick up a Wii U for reasons other than Project X, SMTxFE, and SSB

Im so looking forward to this game and E3 cant come soon enough. Having said that I expect nothing but some high quality visuals as what Miyamoto said in a youtube interview from what I can remember. I was really satisfied with the art sytle they showed off at E3 2011 as it was the perfect combination between the cartoony and the hyper realistic look - neither of which suit the zelda series in my opinion.

story is more important to me than graphics. although I have no doubt it'll look superb. I want something DARK for the story, post apocalypse, travelling back in time to halt the destruction dark.

If its like WW, no thanks.

If its like the wiiu tech demo, then hell yeah!

You're the perfect example of why Nintendo needs to make this Zelda less kiddy. Because a lot of gamers think like you do.

Don't forget about tessellation, that's where it at, really, for any game to be considered truly great.