Limit SQL query conditions based on a column

Question

I have two tables, one is called user and the other is called payment. A user can have multiple payment records. For example: User 1 has 2 payment records User 2 has 5 payment records User 3 has 10 payment records User 4 has 7 payment records

I have the following query:

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

This query will only return user 1 and his 2 payment records.

But I want to return user 1 and user 2 with their payment records respectively.

Answer 1

If I understand correctly, you want to return payments for both 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