Count with zero-results

Hi,

The following situation:

I have a state table wich defines 5 different states a proces can have. Fields:

ID, description

Further another table with a lot of fields plus:

state (key with ID from state-table) and a period (a combined year/month value)

Now I'm looking for a query that calculates how many states occur per month.

with:

SELECT h.period, s.description, COUNT(h.state) as Amount FROM state_table s
LEFT JOIN h_table h
ON h.state=m.ID
WHERE h.period >= 200601
AND h.period <= 200612
GROUP BY period, m.omschrijving

give me the result of the amount>0. I would like to have per month (period) a matrix include the states that doesn't occur in that month

So Result:

200601 state 1 2
200601 state 3 7
200601 state 4 1
200602 state 1 3
etc

must be:

200601 state 1 2
200601 state 2 0
200601 state 3 7
200601 state 4 1
200601 state 5 0
200602 state 1 3
etc

Can somebody help me?

thanks Received on Tue Aug 22 2006 - 05:32:53 CDT