I have a simple (hopefully) SQL question for you, any help would be much, much appreciated :).
I have two tables which I would like to join.
One Is Users, lets say it’s called users
One is a kind of history of that user, lets say its called users_history.
The relationship of these two is a one users to many users_history relationship.
What I’d like to do is a query which joins the tables and joins the newest record in users_history onto each user.
Lets say the tables are like this, I’m simplifying for conciseness.
The date is formatted YYYYMMDD.
The end result is I’d like to be able to pull out all of the users who don’t have a users_history record for today, for example today is 20101021.
Any help would be very gratefully received! 🙂
SELECT MAX(users_history.date), users.name FROM users LEFT JOIN users_history ON users.id = users_history.user_id GROUP BY users_history.user_id HAVING MAX(users_history.date) < CURDATE()
If you dont want users who doesnt have eny users_history records in the resultset, change the “LEFT JOIN” to a “JOIN”
If all you really want is finding the users without a
user_history entry for today, you can use a subquery like so:
SELECT * FROM users WHERE NOT EXISTS (SELECT id FROM users_history WHERE user_id = users.id AND `date` = DATE(NOW()));
IMHO, this is more readable than a join and some filtering in your host language.
edit: Judging from your date format description, you probably use a
varcharcolumn to store the date. In that case, replace
DATE(NOW()) with the appropriate string representation for “today” – though I’d recommend changing the column type to a date/time type.
pull out all of the users who don’t have a users_history record for today, for example today is 20101021.
select u.* from users u left outer join users_history uh on u.id = uh.user_id and uh.history_date = curdate() where uh.user_id is null;