MySql - Problems in a query construct

I need your help....I'm working on a little Time Management Sytem for my compagny.

I have several tables, including this two tables :

Pointage

Id_check | id_user | week | time | …….

Users

Id_user | first_name | last_name | ………

I would like find a means to construct a report which give me all people who didn't check 5 days for last weeks. For example

Id_user | week | time

So I have created a query like that :

SELECT week,id_user,SUM(time) AS totalW FROM pointage WHERE week<42
GROUP BY id_user,week HAVING totalW<5 ORDER BY id_user

My problem is that this query give me lates only if the person has checked at least one time (for a week).

For example, if the id_user '1' don't check any time for the week 40, he won't appear in my report. An important problem for a query which should give me all people in late in their checks. He 开发者_开发技巧will be appeared if he had checked at least one time, for example 1 day.

I have tried to modify my query, I have created a new table 'week', join it with LEFT / RIGHT JOIN but I don't find any solution to solve my wish !

So my last chance is to post this message !

Do you have an idea to obtain this report ?

Thanks very much for your help and sorry for my bad english !

Nicolas

select week.week, users_id_users, 
(
   select 
   if(sum(time) is null, 0, sum(time)) 
   from pointage 
   where users.id_user=pointage.id_user and pointage.week=week.week 
   group by pointage.id_user 
   having count(*)<5
) as sum_time 
from users, week
where week.week<42

• assuming your week table contains record from week 1..52