Dates and logic help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Dates and logic help

Post 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..
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Dates and logic help

Post 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 )
 
Last edited by GeXus on Thu Feb 21, 2008 1:53 pm, edited 1 time in total.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Dates and logic help

Post 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.
Last edited by Zoxive on Thu Feb 21, 2008 1:59 pm, edited 1 time in total.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Dates and logic help

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Dates and logic help

Post 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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Dates and logic help

Post 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...
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Dates and logic help

Post 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!!
Post Reply