Howdy... So I have a problem that I'm not quite sure how to solve. Here it is.
I have two tables
1) Users
- id
- name
- active
2) Messages
- id
- user_id
- sent_date
I am trying to select all of the user id's from the user's table, where the total number of messages are less than 8 in the past seven days.
Any tips on how I might do this? There will be cases where there are no messages at all for a particular user..
Dates and logic help
Moderator: General Moderators
Re: Dates and logic help
Here is what I have... but the problem with this is if there are no records in the messages table for a user, it returns nothing...
Code: Select all
SELECT a.id
FROM users a
LEFT JOIN messages b ON a.id = b.user_id
WHERE DATE_SUB( b.sent_date, INTERVAL 7
DAY )
Last edited by GeXus on Thu Feb 21, 2008 1:53 pm, edited 1 time in total.
Re: Dates and logic help
Working as expected, you would then be joining it with nothing. Making the query return nothing.GeXus wrote:Here is what I have... but the problem with this is if there are no records in the messages table for a user, it returns nothing...
Edit: False statement, it is the WHERE making it return nothing.
Last edited by Zoxive on Thu Feb 21, 2008 1:59 pm, edited 1 time in total.
Re: Dates and logic help
How would you suggest doing it?Zoxive wrote:Working as expected, you would then be joining it with nothing. Making the query return nothing.GeXus wrote:Here is what I have... but the problem with this is if there are no records in the messages table for a user, it returns nothing...
Re: Dates and logic help
I Can't really test it, but Try adding an OR that counts the messages, which would be 0.GeXus wrote: How would you suggest doing it?
Joins that don't succeed values are null, so you could check if b.id is null also.
Re: Dates and logic help
Ok, I tried this...
But I go an error 'Invalid use of group function'... So i guess I can't have count in a where clause...
I also tried...
With b.id = NULL, but that didn't return any results... (which there are), other than those that exist in the messages table...
Code: Select all
SELECT count( b.id ) AS count, a.id
FROM users a
LEFT JOIN messages b ON a.id = b.user_id
WHERE b.created_dt >= DATE_SUB( CURRENT_DATE, INTERVAL 7
DAY )
OR count( b.id ) = ''
GROUP BY user_id
I also tried...
Code: Select all
SELECT count( b.id ) AS count, a.id
FROM users a
LEFT JOIN messages b ON a.id = b.user_id
WHERE b.created_dt >= DATE_SUB( CURRENT_DATE, INTERVAL 7
DAY )
OR b.id = NULL
GROUP BY user_id
Re: Dates and logic help
Code: Select all
SELECT count( b.id ) AS count, a.id
FROM users a
LEFT JOIN messages b ON a.id = b.user_id
WHERE b.created_dt >= DATE_SUB( CURRENT_DATE, INTERVAL 7
DAY )
OR b.id IS NULL
GROUP BY user_id
LIMIT 0 , 30