MySQL中通过公共父列关联多表查询：合并会员与会员项目数据

Got it, let's work through this. You need a query that pulls each member's core details alongside all their membership programs packaged into a single aggregated object. Here's how to do it cleanly with MySQL's JSON functions (available in 5.7 and later—this is the most straightforward approach):

The Query #

SELECT
    m.ID AS MemberID,
    m.FirstName,
    m.LastName,
    m.Gender,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'ID', ms.ID,
            'Program', ms.Program,
            'DateStarted', ms.`Date Started`,
            'DateExpired', ms.`Date Expired`,
            'MemberID', ms.MemberID
        )
    ) AS Memberships
FROM Member m
INNER JOIN Membership ms ON m.ID = ms.MemberID
GROUP BY m.ID, m.FirstName, m.LastName, m.Gender;

Breakdown of the Query #

• Table Join: We use an INNER JOIN to link the Member and Membership tables via the MemberID foreign key—this ensures we only get members who have at least one membership (swap with LEFT JOIN if you want to include members with no memberships, which would return NULL for the Memberships field).
• JSON Object Creation: JSON_OBJECT() converts each individual membership record into a structured JSON object. I adjusted space-containing column names like Date Started to DateStarted for cleaner JSON keys, but you can keep the original names by wrapping the column names in backticks `.
• Aggregation: JSON_ARRAYAGG() takes all the JSON objects for a member and bundles them into a single JSON array, which we alias as Memberships—this is your combined "Membership object" for each member.
• Grouping: We group by all the member's columns to ensure we get one row per member, with all their memberships neatly packaged together.

Example Result #

Running this with your sample data will give you a result like this:

For Older MySQL Versions (Pre-5.7) #

If you can't use JSON functions, you can use GROUP_CONCAT to build a string-based representation of the memberships:

SELECT
    m.ID AS MemberID,
    m.FirstName,
    m.LastName,
    m.Gender,
    GROUP_CONCAT(
        CONCAT(
            '{ID:', ms.ID,
            ', Program:"', ms.Program,
            '", DateStarted:"', ms.`Date Started`,
            '", DateExpired:"', ms.`Date Expired`,
            '", MemberID:', ms.MemberID, '}'
        ) SEPARATOR ', '
    ) AS Memberships
FROM Member m
INNER JOIN Membership ms ON m.ID = ms.MemberID
GROUP BY m.ID, m.FirstName, m.LastName, m.Gender;

Note this returns a string instead of proper JSON, so you'll need to parse it in your application code if you want structured data.

内容的提问来源于stack exchange，提问作者The Bassman