Page 1 of 1
Dates and logic help
Posted: Thu Feb 21, 2008 1:40 pm
by GeXus
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..
Re: Dates and logic help
Posted: Thu Feb 21, 2008 1:46 pm
by GeXus
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 )
Re: Dates and logic help
Posted: Thu Feb 21, 2008 1:48 pm
by Zoxive
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...
Working as expected, you would then be joining it with nothing. Making the query return nothing.
Edit: False statement, it is the WHERE making it return nothing.
Re: Dates and logic help
Posted: Thu Feb 21, 2008 1:53 pm
by GeXus
Zoxive wrote: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...
Working as expected, you would then be joining it with nothing. Making the query return nothing.
How would you suggest doing it?
Re: Dates and logic help
Posted: Thu Feb 21, 2008 2:01 pm
by Zoxive
GeXus wrote:
How would you suggest doing it?
I Can't really test it, but Try adding an OR that counts the messages, which would be 0.
Joins that don't succeed values are null, so you could check if b.id is null also.
Re: Dates and logic help
Posted: Thu Feb 21, 2008 2:27 pm
by GeXus
Ok, I tried this...
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
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...
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
With b.id = NULL, but that didn't return any results... (which there are), other than those that exist in the messages table...
Re: Dates and logic help
Posted: Thu Feb 21, 2008 2:33 pm
by GeXus
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
Ok, I was checking the null wrong.. you were right.. this worked! Thank you!!