first off Alex let me apologize for my bluntness... your probably not going to like this answer < g >

>How do you handle this sort of thing?

I go back and look at my database design. Especially if I run into this more than once in an app... the data structures most not be setup in a fashion that is optimal for the application to serve it's purpose. Often times this can lead to the decision to denormalize for the sake of performance. Before you make that decision you should really let your users/client tell you performance is an issue and not just guess about it.

If your specific scenario requires this complex view to be updateable, then I'd of taken a long hard look at the data model long ago. If your doing this complicated of a query just for lookup purposes, then model the data to optimize that purpose.

Plus too I seen elsewhere you've posted about considering to put some lookup data on the client at startup for optimization... another indicator in my book the data isnt modeled appropriately. A lookup-intensive app is bound to need functionality outside of the normalized relation data paradigm, that's just how life is in some environments.

Do you have any control over the data? Can you change how the tables are structured or are your hands tied here?