Mysql one-to-many can easily track the first record in history

background

• When processing one-to-many relationships in a database
• We often need to find specific records in subtables (e.g., records in table B) for each main table record (e.g., each record in table A)
• For example, the earliest record

Environment settings

Suppose we have two tables:

• A and B.
• Table A has a primary key id, and table B has a foreign key id pointing to the id of table A, forming a one-to-many relationship.

There is also a timestamp field in table B created_at to record the creation time of the record.

We use the JOIN operation and the WHERE clause to solve this problem.

• This approach involves comparing table B with itself
• To make sure we select the record of each corresponding earliest created_at time

SELECT b1.*
FROM B b1
JOIN A a ON  = 
WHERE b1.created_at = (
    SELECT MIN(b2.created_at)
    FROM B b2
    WHERE  = 
);

principle

The work principle of this query is as follows:

External query

• We're fromBTable (aliased asb1)andATable (aliased asa) start, byJOINThe operation connects them, the connection condition is = 。
• Assume hereBTable ofidThe field actually points toAThe foreign key of the table.

Subquery in WHERE clause

• existWHEREIn the clause, we use oneSubqueryCome find outBEach in the tableidThe earliest correspondingcreated_attime.
• This subqueryBTable (aliased asb2) Select from )created_atfield, and according toidGrouping, usingMIN()The function finds the earliest time.

Match the earliest record

• External queryWHEREClauses make sure only those are selected
• created_atRecords whose time matches the earliest time returned by the subquery.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.