MySQL date related query

Question

I am having problems understanding how best to tackle this query.

I have a table called user_files. A user can have many files. I want to get a list of users who have not modified any of their files within the last year. If a user has modified at least one of their files within the last year, then that user should be excluded.

  Table: user_files
  file_id | user_id | date_modified
  ----------------------------------
  1        100       2010-10-01
  2        100       2010-11-13
  3        100       2011-01-01
  4        101       2010-10-01
  5        101       2010-06-13
  6        101       2011-04-12
  7        101       2012-04-01

The expected result would only list user_id 100.

Here is some bad sql I have been playing with. The idea is that I find all users who recently modified their files and then find users who are not included in that list.

    select user_id from users where user_id not in
    (
      select user_id from 
      (
        select user_id, story_id, max(date_modified) from user_files 
        where DATE_FORMAT(date_modified, '%Y-%m-%d') >= DATE_SUB(curdate(), INTERVAL 1 YEAR)
        group by user_id
      )x
    )

Thanks

lc. · Accepted Answer · 2012-07-12 19:04:00Z

2

SELECT DISTINCT(f.user_id)
FROM user_files f
WHERE NOT EXISTS(SELECT 1
                 FROM user_files ff
                 WHERE ff.user_id = f.user_id
                 AND ff.date_modified >= DATE_SUB(curdate(), INTERVAL 1 YEAR))

http://sqlfiddle.com/#!2/64e7f/1

Or,

SELECT user_id
FROM user_files
GROUP BY user_id
HAVING MAX(date_modified) < DATE_SUB(curdate(), INTERVAL 1 YEAR)

http://sqlfiddle.com/#!2/64e7f/4

edited Jul 12, 2012 at 19:04

answered Jul 12, 2012 at 18:58

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tollbooth Over a year ago

Cool. Didn't know about sqlfiddle.com.

Zane Bien · Accepted Answer · 2012-07-12 18:57:43Z

0

You can use this simple solution:

SELECT a.user_id
FROM users a
LEFT JOIN user_files b ON 
    a.user_id = b.user_id AND 
    b.date_modified >= CURDATE() - INTERVAL 1 YEAR
WHERE b.user_id IS NULL

answered Jul 12, 2012 at 18:57

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL date related query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related