CODEWITHSUNDEEP
mysqlsqljoin
False
False

Reputation: 61

Mysql get the latest row on join/select a table (1:n)

i have the table "user" (Primary key is id) and the table "user_meta" (Primary key is user_id and valid_from). The user table contains basic user data e.g. username, password, etc. The user_meta contains possible changing data e.g. lastname, gender(yea its 2018 :D) etc. So i have a history on which day which data are valid.

My Problem ist that i try to select all user with the currently valid data, but i failed often...

How i can select the correct data ?

For one user i can simply use

"select * from user_meta 
  JOIN user on user_meta.user_id = user.id 
  ORDER BY valid_from DESC LIMIT 1"

but how its working with multiple/all users?

greetings, False

Upvotes: 0

Views: 55

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

you could use a join on a subselect for max_valid group by user 

select * from user_meta
inner join (
    select user.id, max(user_meta.valid_from) max_valid
    from user_meta 
    JOIN user on user_meta.user_id = user.id 
    group by user.id
) t on t.id= user_meta.user_id and t.max_valid = user_meta.valid_from

or more simple 

select * from user_meta
inner join (
    select user_meta.user_id, max(user_meta.valid_from) max_valid
    from user_meta 
    group by user_meta.user_id
) t on t.user_id= user_meta.user_id and t.max_valid = user_meta.valid_from

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

You probably want something along these lines:

SELECT u.*, um.*
FROM user u
INNER JOIN user_meta um
    ON u.id = um.user_id
INNER JOIN
(
    SELECT user_id, MAX(valid_from) AS max_valid_from
    FROM user_meta
    GROUP BY user_id
) t
    ON um.user_id = t.user_id AND
       um.valid_from = t.max_valid_from;

Not much to explain here, except that the subquery aliased as t will filter off all metadata records except for the latest one, for each user.

Upvotes: 2

Related Questions