CODEWITHSUNDEEP
mysqlsql
flowfree
flowfree

Reputation: 16462

How to SELECT rows on a table with COUNT from other table in a range of time?

I have these tables:

users
-----
id                  INT
name                VARCHAR(20)
email               VARCHAR(40)

user_fans
----------
id                  INT
user_id             INT  /* linked to users.id */
fan_id              INT  /* linked to users.id */
time_created        INT  /* unix timestamp */

I can get all rows from table users with additional field named num_fans using the following query

SELECT u.id, u.name, u.email, COUNT(f.id) AS num_fans
FROM users u
LEFT JOIN user_fans f ON u.id=f.user_id
GROUP BY u.id, u.name, u.email
ORDER BY num_fans DESC

The problem is I need to get the num_fans in a range of time. I tried this query

SELECT u.id, u.name, u.email, COUNT(f.id) AS num_fans
FROM users u
LEFT JOIN user_fans f ON u.id=f.user_id
WHERE f.time_created > UNIX_TIMESTAMP('2012-5-1 00:00:00')
GROUP BY u.id, u.name, u.email
ORDER BY num_fans DESC

But the query above will return only users which already have fans. I want the rest of users also returned with num_fans=0.

Thanks for your help.

Upvotes: 0

Views: 125

Answers (2)

AlexDev
AlexDev

Reputation: 4717

Try adding is null to the condition:

SELECT u.id, u.name, u.email, COUNT(f.id) AS num_fans  
FROM users u  
LEFT OUTER JOIN user_fans f ON u.id=f.user_id  
WHERE f.time_created is null or f.time_created > UNIX_TIMESTAMP('2012-5-1 00:00:00')  
GROUP BY u.id, u.name, u.email  
ORDER BY num_fans DESC

Upvotes: 0

Nivas
Nivas

Reputation: 18354

Move the condition to the on:

SELECT u.id, 
       u.name, 
       u.email, 
       COUNT(f.id) AS num_fans 
FROM users u LEFT JOIN user_fans f 
             ON u.id=f.user_id 
             and f.time_created > UNIX_TIMESTAMP('2012-5-1 00:00:00') 
GROUP BY u.id, u.name, u.email ORDER BY num_fans DESC

Update
(Added an explanation on @Gardon Dave's suggestion)

The difference between what you have and this is:

In your query you have the condition in the WHERE clause. This is applied after the Join,(see this as filtering on top of the join). If a row does not follow this condition it will be eliminated. Outer joined rows that do not have an entry in the user_fans table will not satisfy this condition (value will be NULL).

Having the condition in the on will join the users table only to the subset of the user_fans table - the outer join happens after this filtering.

Upvotes: 2

Related Questions