MySql - Problems in a query construct

Question

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

I didn't know the "functionnality". I will accept the future answers now ! — sanceray3
– sanceray3, Commented Oct 29, 2010 at 8:33

ajreal · Accepted Answer · 2010-11-08 14:53:55Z

1

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

answered Nov 8, 2010 at 14:53

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sanceray3 Over a year ago

Thank you very much, it functions with some modifications : select week.id_week, users.id_user,IFNULL( ( select sum(time) from pointage where users.id_user=pointage.id_user and pointage.week=week.id_week group by pointage.id_user ),0) as sum_time from users, week where week.id_week<42

Collectives™ on Stack Overflow

MySql - Problems in a query construct

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related