Reputation: 2452
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
Upvotes: 0
Views: 32
Reputation: 33945
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;
Upvotes: 0
Reputation: 31785
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`
Upvotes: 2