My apologies for the 'gents' salutation. What was I thinking.

Thanks for the response. I just created something to mimic what I have currently. But yes I do need all tables and the league table can have duplicates hence the union instead of Union all. I also noticed you still had the union even after the outer apply replacement.

In the real scenario, all the unions share at least 3 tables in common. Is there any way to avoid the unions completely?