MySQL Help

visibleman · 30 Nov 2015 at 21:55

Hi All,

I've been rattling my brains for most of the day and can't seem to figure out why a solution to my issue.

Essentially i have two tables, one called 'users' -

Code:

+----------+------------+
| USERS_ID | USERS_NAME |
+----------+------------+
|          |            |
| 1        | Dave       |
|          |            |
| 2        | Bob        |
|          |            |
| 3        | Jack       |
+----------+------------+

and another called, 'visits' -

Code:

+----------+----------+------------+
| VISIT_ID | USERS_ID | VISIT_DATE |
+----------+----------+------------+
|          |          |            |
| 1        | 3        | 2015-11-23 |
|          |          |            |
| 2        | 1        | 2015-11-24 |
|          |          |            |
| 3        | 3        | 2015-11-25 |
|          |          |            |
| 4        | 3        | 2015-11-24 |
|          |          |            |
| 5        | 1        | 2015-11-26 |
|          |          |            |
| 6        | 3        | 2015-11-27 |
+----------+----------+------------+

And what i'm wanting is the weeks totals (include no results, ie - zero) to be displayed against the 'users' table like so -

Code:

+-----------+-------+
| USERS_NAME | TOTAL |
+-----------+-------+
|           |       |
| Dave      | 2     |
|           |       |
| Bob       | 0     |
|           |       |
| Jack      | 4     |
+-----------+-------+

Current query is -

Code:

SELECT `users`.`users_name`, COUNT(`visits`.`users_id`) AS `weekly_total` FROM `users` LEFT JOIN `visits` ON `users`.`users_id` = `visits`.`users_id` WHERE (`visits`.`visit_date` >= '2015-11-23' AND `visits`.`visit_date` <= '2015-11-27') GROUP BY `visits`.`users_id`, `users`.`users_id`

However because of the date range condition, only users with positive totals (> 0) are being displayed or nothing is displayed if there are no visits within that date range; removing the condition displays all of the users with their 'weekly_totals', include 'zero' totals, but obviously i need the date condition.

I don't doubt it's something obvious which i'm not seeing but is there a way to force the output of all the users table with zero 'weekly_total` regardless of there being data in the 'LEFT' table due to the date condition?

Much appreciated for any help or pointers!

radderfire · 30 Nov 2015 at 22:14

I've had a quick look and I might be wrong but ...

The problem is with the LEFT JOIN

... if a user made no visits, then they won't be meeting the:

`users`.`users_id` = `visits`.`users_id`

statement, because their users_id is not in the visits table.

Therefore, you may need to change your JOIN type, or somehow change this bit of the statement:

`users`.`users_id` = `visits`.`users_id`

as you actually want all users_id and not just the ones appearing in the visits table.

visibleman · 30 Nov 2015 at 22:29

Cheers radderfire but unfortunately i already thought of that but using an inner join returns zero results - i suspect the date condition still comes into play.

Ideally, i just need to force it to display all the users but i can't think of anything off the top of my head. Only thought i've got is to use a subquery on the COUNT() function to count the rows in the 'visits' table but i'm not sure that'll work properly.....

Edit - Well a subquery column does appear to work -

Code:

SELECT `users`.`users_name`, (SELECT COUNT(*) FROM `visits` WHERE `visits`.`users_id` = a.`users_id` AND (`visits`.`visit_date` >= '2015-11-23' AND `visits`.`visit_date` <= '2015-11-27')) FROM `users` a WHERE 1

I'm convinced there's a better, more efficient way of doing this without subqueries though but either way, it works i guess.

Cheers for the help though radderfire

planty · 1 Dec 2015 at 14:03

Can you just UNION your original query with some 'initialised' data?

Code:

SELECT 
`users`.`users_name`, 
COUNT(`visits`.`users_id`) AS `weekly_total` 
FROM `users` 
LEFT JOIN `visits` ON `users`.`users_id` = `visits`.`users_id` 
WHERE (`visits`.`visit_date` >= '2015-11-23' AND `visits`.`visit_date` <= '2015-11-27') 
GROUP BY `visits`.`users_id`, `users`.`users_id`

UNION 

SELECT 
`users`.`users_name`,
0 
FROM `users`;