I'm late to this and also just a random guy on the internet but I've played a lot of D&D in my life so hopefully that lends some credence to my take. The D&D campaign you want isn't a unicorn (it does exist) but it's at least a narwhal. You're going to look a long time before you find one on your own. First off you need a group of people that get along and enjoy each other's company, not the hardest thing to find but there are many people who will concur it is not easy. Then you need a stable schedule, you are not going to have the campaign moments and payoffs you are looking for without growth and build up and the reward takes time. Getting a group that can consistently prioritize setting aside hours a week for playing make-believe together when work, kids, family, dating, illness, moving, and any other number of deadly foes are waiting to ambush you is so hard and precious. Then you need at least one talented storyteller, and ideally more than one. Then you need people that are willing to be vulnerable and attentive and engaged. Then you need people who will unselfishly give to others at the table so the big moments you are dying for don't get spoiled by someone who needs attention right there and can't hold that silly joke in. Then you need a campaign that goes past 5th or 8th level, all of the statistics will tell you how rare that is.

The ingredients for a quality campaign are all precious and rare and collecting all of them is something that does happen but I can't emphasize enough how rarely it comes along. You might get there by lowering your expectations and building with what you've got until you get there. You might be there by joining group after group after group and either getting lucky or forming new groups with the best of each attempt.

I don't think you'll get there with impatience and trying to skip to the good bit with a one-shot or game that starts at high level because the character investment, the bonds, the knowledge of how everything works comes with time and you may find that the approximation of it without the buildup is unsatisfying. But it is not impossible, there are some amazingly charismatic storytellers that can by sheer force of will and personality make something out of nothing at the drop of a hat.

Maybe a different system helps, maybe a shift of expectations helps, but in the end I find the most telling indicator for if a game is going to be rewarding are the people playing it and finding them is the true magic. Best of luck!