how to display end of line with join mysql

user_9279902 0
Viewed 36

I have 3 tables:

  1. table user
  2. table activity a
  3. table activity b

I want to retrieve all the data in the user table and all the last data in the table of activities a and b, like this : overview result

how do i do sql writing?

mysql sql
1 Answers

In MySQL, I would recommend using row_number() and left join:

select u.*, a.*, b.*
from user u left join
     (select a.*, 
             row_number() over (partition by id_user order by date_a desc) as seqnum
      from a
     ) a
     on a.id_user = u.id_user and a.seqnum = 1 left join
     (select b.*, 
             row_number() over (partition by id_user order by date_b desc) as seqnum
      from b
     ) b
     on b.id_user = u.id_user and b.seqnum = 1;
user_11440350
Related