Joined two tables & get results

Question

I'm having some trouble with joining two mysql tables & getting the result as I want.

I have two tables , users table & times table. Users table is having id & name . time table is having user_id & minutesSpent. I want to get the times for all the users with name & times column as the result set. But for some specific dates all user ids are not in the times table. So I need null value as for such users.

I have tried several queries, but every time I'm getting times only for user_ids available in the times table. Not getting other user_id's times as null :(

SELECT `users`.`name`,SUM(`times`.`minutesSpent`) AS `Total` 
FROM `users` 
LEFT OUTER JOIN `times`  ON `users`.`id` = `times`.`user_id`   
WHERE DATE(`date`) = '2015-06-03' 
GROUP BY `users`.`name`

& I have tried this query as well

SELECT `users`.`name`,SUM(`times`.`minutesSpent`) AS `Total` ,CASE WHEN times.user_id IS NULL THEN 0 ELSE 1 END 
FROM `users` 
INNER  JOIN `times`  ON `users`.`id` = `times`.`user_id` AND `users`.`isTimeEnable` = 0   
WHERE DATE(`date`) = '2015-06-03' 
GROUP BY `users`.`name`

if any one help me with this, it would be great . Thanks

Answer 1

Your WHERE clause is turning your left join into an inner join. Try this:

   SELECT `users`.`name`,SUM(`times`.`minutesSpent`) AS `Total` 
    FROM `users` 
    LEFT OUTER JOIN `times`  ON `users`.`id` = `times`.`user_id`   
      AND DATE(`date`) = '2015-06-03' 
    GROUP BY `users`.`name`

Answer 2

Incidentally, if less typing (well 20 characters) and better performance is your thing then consider the following:

SELECT u.name
     , SUM(t.minutesSpent) Total 
  FROM users u 
  LEFT 
  JOIN times t
    ON u.id = t.user_id   
   AND t.date BETWEEN '2015-06-03 00:00:00' AND '2015-06-03 23:59:59' 
 GROUP 
    BY u.name;