>Hi
>
>this is actually a Mysql query.
>
>I have a table with a range of yearmonths (say 200501 to 200702)
>
>and a table with yearmonth,model_id,amount_sold with some yearmonths for some models missing if none where sold in the month.
>
>I want a query which gives me all yearmonths in my range, all models and amounts_sold including 0 months where none of a particular model has been sold.
>
>Any thoughts on the easiest way to achieve that.
>
>Thanks
>
>Nick

Select C.YearMonth, C.Model_ID, sum(Amount_Sold) as TotalAmountSold
from (select YearMonth, Model_ID from YearMonths,Models 
where YearMonth between @StartRange and @EndRange) C
LEFT JOIN Trans T on C.YearMonth = T.YearMonth and C.Mode_ID = T.Model_ID
GROUP BY C.YearMonth, C.Model_ID

The main thing here is to first get all possible combinations of yearmonths and models by cross joining yearmonths with Models and then use this result to LEFT JOIN with your actual table with data.