mysql join question, joining newest record in a one to many relationship.

Question

Hi all,

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.

users

• id
• name

users_history

• id
• user_id
• date

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! :)

Answer 1

Try

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"

Answer 2

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.

Answer 3

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;