There are, indeed, other approaches that will probably be faster than the CROSS JOIN approach (like Eirikur's great shot at this). My concern is that I said that the indexes were essential. In the case of the CROSS JOIN versions to solve this problem, they're not really so essential. They just make things run a bit faster. In the "count" type of solutions, the UNIQUE index on the bridge table is absolutely essential to accuracy. While that may not seem like it should ever be a problem, I've personally witnessed an idiot "developer" pitch a fit to an even bigger idiot "DBA" that he keeps getting primary key violations on the bridge table and that moroff of a "DBA" removed the unique index from the bridge table. I wanted to kill him on the spot to make sure that very shallow gene pool ended right there and then. 🙂

Of course, the duplicated entries into the bridge table broke all of the code that did such calculations as what you've identified.

I love speed. To me, it's one of the most important factors in the world of databases. The only thing more important to me than speed is accuracy. My recommendation is that when it comes to accuracy, expect the unexpected and make sure it will be bullet-proof for accuracy over the long haul even if the code takes longer to run.