I am not sure of the difference but I do know I could not seem to get my query with the middle table to work as it was set up for my other example that had just 2 tables.

Not sure why yet, but will look at it more closely.

Thanks,

Tom

Hi Tom

In both queries the intent is to build an intermediate table which is a cross of DealerID and ForecastID, to which you can LEFT JOIN other tables. This ensures that you're starting with the whole picture - every dealer has every forecast.The structural difference between the two queries is to accommodate the changes in the sample tables and data you've provided.

I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

You're way works but it is different.

You've got a little defensive there and having re-read what I wrote I can see why. I hadn't intended what I wrote to sound like I was accusing you of not paying attention, I simply wanted to draw your attention to the fact that the logic behind the idea is identical in both your previous question and your new question. As Chris explained (far better than I), the difference in the structure of the query is to accommodate the changes to the schema that you supplied with your question.

I understand what Chris had explained about the CROSS JOIN. I was trying apply that to the situation I have which I couldn't make work so I have been trying to start from the basic layout and add complexity to it to understand the process better.

I have one more scenario that I am trying to put together that actually is one more table past this that is pretty much what my actual layout is like.

I am trying to see if I can make it work first before I add the extra table to the sample to see if I can make it work.

I understand what Chris had explained about the CROSS JOIN. I was trying apply that to the situation I have which I couldn't make work so I have been trying to start from the basic layout and add complexity to it to understand the process better.

I have one more scenario that I am trying to put together that actually is one more table past this that is pretty much what my actual layout is like.

I am trying to see if I can make it work first before I add the extra table to the sample to see if I can make it work.

Thanks,

Tom

Can you post it here Tom with DDL and perhaps some sample data? It's time we put this one to bed