I'm not really seeing what your objective means in real-world terms. You seem to want some kind of bridge between your dimension table and your fact table, but I have no clear picture from either your queries or your post as to what that relationship needs to be. If you could express that in words that describe real-world events, it would be much simpler to understand your objective, as we don't really know what kind of record counts exist in your real tables, nor any idea why there's a problem brewing.