A SQL Join

March 16, 2006 3:03 PM

How do I manage this SQL join?

I have two tables: USERS and LOGGED_IN_USERS created as follows (non-relevant columns removed)

CREATE TABLE Users
(
user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
remember TINYINT(1) NOT NULL
)

CREATE TABLE LoggedInUsers
(
user_id INTEGER PRIMARY KEY,
session_id VARCHAR(255) UNIQUE NOT NULL,
last_access DATETIME NOT NULL,

FOREIGN KEY (user_id) REFERENCES Users (user_id)
)

What I want to do is write a query that deletes rows from the LoggedInUsers whose last_access is more than some variable $interval hours old and whose value in the Users.remember column is 0.

This query works fine to delete old logins

DELETE FROM LoggedInUsers
WHERE last_access <= DATE_SUB(NOW(), INTERVAL $interval)

Now I want to add the condition that the DELETE only happen if the 'remember' value in table Users is 0 for the row with the same user_id.

posted by nonmyopicdave to Computers & Internet (13 answers total)

Which database are you using? MySQL, SQL Server, Oracle, etc?
posted by Bezbozhnik at 3:05 PM on March 16, 2006

You could use a subselect, i.e.:

DELETE FROM LoggedInUsers
WHERE last_access < date_sub(now(), interval $interval)br> AND user_id in
(select user_id from Users where remember = 0)
posted by valkyrie at 3:10 PM on March 16, 2006

Response by poster: The database is MySQL.

Thanks valkyrie. I'll give it a shot (sans "br>") and report back.
posted by nonmyopicdave at 3:17 PM on March 16, 2006

Best answer: Hmm, I don't think MySQL supports subselects at this time, I know it didn't a couple years ago.

Why not just be lazy and add a 'remember' column to logged_on_users?

It will actually make the query run much faster, because it will only need to lookup one table during the query.
posted by Paris Hilton at 3:59 PM on March 16, 2006

In fact, why not just be really lazy and add those values to the users table? then it would just be

update users set logged_on = 0 where last_access < now() - @intervalbr>
or whatever in MySQL
posted by Paris Hilton at 4:02 PM on March 16, 2006

huh, how did that br get there, weird.

update users set logged_on = 0 where last_access < now() - @interval

ah, it was my less-than sign. heh.
posted by Paris Hilton at 4:02 PM on March 16, 2006

MySQL supports subselects as of 4.1.*
posted by fishfucker at 4:03 PM on March 16, 2006

Best answer:
If you're working with 4.0 this should work:

DELETE FROM LoggedInUsers USING LoggedInUsers, Users WHERE last_access < date_sub(now(), interval $interval) and loggedinusers.user_id=Users.user_id and users.remember=0 br>
I'm a mysql neophyte though, so I'd recommend testing this on some made up tables.
posted by fishfucker at 4:09 PM on March 16, 2006

Response by poster: You know Paris, that might be a better solution-- add the 'remember' column to LoggedInUsers. Because it's not a user who wants their login to not expire, but rather a user's specific session.

Genius!

Incidentally, my database connection is down, so I can't test the subselect yet.
posted by nonmyopicdave at 4:49 PM on March 16, 2006

Best answer: the syntax is:

delete LoggedInUsers from LoggedInUsers l, Users u where l.last_access < date_sub(now(), interval $interval) and l.user_id=u.user_id and u.remember=0

that should work
posted by karen at 4:50 PM on March 16, 2006

btw, you do not need a subselect since this is a straight join. you just want to make sure it only deletes them from LoggedInUsers and the above syntax I gave should work. I use it all over.
posted by karen at 4:52 PM on March 16, 2006

Best answer: What about:

CREATE TABLE Users
(
user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
remember TINYINT(1) NOT NULL,
logged_in TINYINT(1) NOT NULL,
session_id VARCHAR(255) UNIQUE NOT NULL,
last_access DATETIME NOT NULL,
)

UPDATE Users SET logged_in = 0 WHERE last_access < date_sub(now(), interval $interval) and remember=0;

I think your way is probably better in terms of normalization and disk usage, but mine is better in terms of speed and resources usage. Like anything else, its a trade-off.
posted by ChasFile at 7:35 PM on March 16, 2006

Response by poster: Thanks karen. I decided to stick the 'remember' col in the LoggedInUsers table, so I won't need the join.

ChasFile: Yeah, it seems better to me to separate the User info, which may be a large table, with the current session info, which will be smaller. And that LoggedInUsers gets hit more.
posted by nonmyopicdave at 8:31 PM on March 16, 2006