Question 187233 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I want to limit my SQL query based on a column

I have two tables, one named user and the other named payment. A user can have multiple payments.
example:
user 1 with 2 payments
user 2 with 5 payments
user 3 with 10 payments
user 4 with 7 payments

I have this query:

select * from user inner join payment on payment.user_id = user.id limit 2

The query will return only user 1 with his 2 payments.

But I want to return user 1 and user 2, each with their payments.

Tags:

2

Answers


  1. 0

    If I understand well, you want to return the payments of two users, if so, try this:

    select p.*
from payment p
inner join (
  select id
  from user
  order by id
  limit 2
) as u on u.id = p.user_id
    Login or Signup to reply.
  2. 0

    If you are running MySQL 8.0.14 or higher, that’s a good spot for a lateral join:

    select u.*, p.*
from users u
cross join lateral (
    select p.*
    from payments p 
    where p.user_id = u.id
    order by p.payment_date desc
    limit 2
) p
where u.id in (1, 2)
order by u.id, p.payment_date

    Notes:

    • you do want to use order by with limit (otherwise the database may not return consistent results); I assumed an payment_date column in the payments table for this purpose, so this gives you the two latest payments per user (for the record, your initial code has the same problem, and there is no guarantee that it will consistently return the same result when ran multiple times against the same dataset)

    • for performance, consider an index on payment(user_id, payment_date desc)

    • when joining multiple tables, it is good practice to enumerate the columns you want in the resultset, rather than using * – this clarifies the intent, and avoids conflicts when columns have the same name in different tables

    • user is a keyword in MySQL, hence a bad choice for a column name (users is not, for example)

    In earlier versions of MySQL 8.0, an alternative is row_number() (which might scale less efficiently if there are many payments per user):

    select u.*, p.*
from users u
inner join (
    select p.*, 
        row_number() over(partition by user_id order by payment_date desc) rn
    from payments p
) p on p.user_id = u.id 
where u.id in (1, 2) and p.rn <= 2
order by u.id, p.payment_date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search