#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Need help with a simple select


    Hi.

    I'm sure this is very simple, but my MySQL knowledge is very limited.

    I got two tables: "sessions" and "users".
    The important part for this in "sessions" is the column "creation_date", which is a datetime, and the "session_id".

    The important part of "users" are the columns "session_id" and "last_activity" (another datetime).

    I want to delete all entries from "sessions" where two clauses are true:
    a) the "creation_date" is older than two days
    b) in the users table, there is no user with that session_id whose "last_activity" is younger than two days.


    So I guess it would be something like:

    DELETE FROM sessions
    WHERE TIME_TO_SEC(TIMEDIFF(NOW(), sessions.creation_date)) / 60 > 1440
    AND {
    SELECT COUNT(*) FROM users
    WHERE TIME_TO_SEC(TIMEDIFF(NOW(), sessions.creation_date)) / 60 > 1440
    AND users.session_id = sessions.session_id } > 0

    Or something like that. I know it's wrong. If anyone could help me to fix it, that would be nice. Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Code:
    DELETE 
      FROM sessions 
    LEFT OUTER
      JOIN users
        ON users.session_id = sessions.session_id 
       AND users.last_activity > CURRENT_DATE - INTERVAL 2 DAY  
     WHERE users.session_id IS NULL
       AND sessions.creation_date < CURRENT_DATE - INTERVAL 2 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo