Join table only few row in MySQL platform

Question

I'm with a problem in table joining. I already search in google but not able to solve this problem. I wanted to get max 4 row in join table. I'm putting here my dummy table structure.

here is table `users`

--------------------
| id | name        |
--------------------
| 1  | John        |
--------------------
| 2  | Mohn        |
--------------------

here is table `user_transections`

------------------------------------------------
| id | user_id | amount  |    created_at       |
------------------------------------------------
| 1  | 1       | 20      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 2  | 1       | 30      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 3  | 1       | 50      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 4  | 1       | 60      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 5  | 2       | 10      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 6  | 2       | 15      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------
| 7  | 2       | 80      | xxxx-xx-xx xx:xx:xx |
------------------------------------------------

I wanted to join only 3 row for each table match of users SELECT user.name,user_transections.amount FROM users INNER JOIN user_transections on user.id = user_transections.user_id // how can i add limit here to join max three row of transections table

how will you choose the 3 records per id? order by max amount or random or any? — jose_bacoy, Apr 15 '18 at 12:30
please see answer below and the answered post found at the top of this SO post. if you have difficulty, let me know at jbacoy3ATyahooDotCom. — jose_bacoy, Apr 15 '18 at 12:50

Gordon Linoff · Accepted Answer · 2018-04-15T14:51:13.127

This is a pain in MySQL. The most general way is to use variables:

select ut.*
from (select ut.*,
             (@rn := if(@u = user_id, @rn + 1,
                        if(@u := user_id, 1, 1)
                       )
             ) as rn
      from (select ut.*
            from user_transections ut
            order by user_id, created_at desc
           ) ut cross join
           (select @u := -1, @rn := 0) params
     ) ut
where rn <= 3;

If you have only two users (which seems unlikely), union all is simpler:

(select ut.*
 from user_transactions ut
 where user_id = 1
 order by created_at desc
 limit 3
) union all
(select ut.*
 from user_transactions ut
 where user_id = 2
 order by created_at desc
 limit 3
);

A third method uses a correlated subquery. Here is one version:

select ut.*
from user_transactions ut
where ut.id >= coalesce( (select ut2.id
                          from user_transactions ut2
                          where ut2.user_id = ut.user_id
                          order by ut2.id desc
                          limit 1 offset 2
                         ), ut.id
                       );

For performance on this query, you want an index on user_transactions(user_id, id).

Thanks, dear third method work for me but it takes huge execution time when I select more thousand entry — Rana, Apr 15 '18 at 13:07
use of variable is more efficient now it's works for me Thanks :D — Rana, Apr 15 '18 at 13:41

Join table only few row in MySQL platform

1 Answers1