Search code examples
mysqlsqljoininner-join

MySQL- JOIN that groups in 1 record the min of a column and the max of another

I have a table of Members (member_id, name, etc..) and a table of Membership (id, member_id, membership starting date, membership expiration date) with a relation of one to many, because every year there is a new entry in Membership, per every member.

When I do :

SELECT * FROM members
INNER JOIN membership ON members.member_id = membership.member_id

I obtain multiple records for every member. How can I group records in only one record per member, that contains the MIN of membership starting date and the MAX of membership expiration date? Thank you.

Solution

  • You could do your aggregation from a derived table of membership. For example:

    SELECT m.*, ms.start_date, ms.exp_date
FROM members m
JOIN (SELECT member_id, MIN(start_date) AS start_date, MAX(exp_date) AS exp_date
      FROM membership
      GROUP BY member_id) ms ON ms.member_id = m.member_id