Introduction

A plain INNER JOIN returns every matching row from the second table, so it cannot by itself choose only one child row in a one-to-many relationship. To get one row, you first need a deterministic rule such as latest, earliest, highest priority, or lowest price. Once that rule is defined, the SQL pattern becomes much easier to write correctly.

Why a Normal Join Returns Too Many Rows

Suppose each order can have many payments. A simple join returns one result row for every matching payment.

1SELECT o.id AS order_id, p.id AS payment_id, p.created_at
2FROM orders AS o
3INNER JOIN payments AS p
4    ON p.order_id = o.id;

If order 10 has three matching payment rows, you will get three result rows for that order. That is correct SQL behavior, but it is not what you want when the requirement says "only one row from the second table".

MySQL 8 Solution with ROW_NUMBER()

In MySQL 8, the cleanest solution is usually a window function. Rank the child rows per parent, then keep only rank 1.

1WITH ranked_payments AS (
2    SELECT
3        p.*,
4        ROW_NUMBER() OVER (
5            PARTITION BY p.order_id
6            ORDER BY p.created_at DESC, p.id DESC
7        ) AS rn
8    FROM payments AS p
9)
10SELECT
11    o.id AS order_id,
12    rp.id AS payment_id,
13    rp.created_at
14FROM orders AS o
15INNER JOIN ranked_payments AS rp
16    ON rp.order_id = o.id
17   AND rp.rn = 1;

This query says: within each order, sort the payments by newest timestamp and then by largest id as a tie-breaker. Keep only the first ranked row.

Pre-MySQL 8 Pattern with Aggregation

If window functions are not available, choose the target row in a subquery and then join back to the real table.

1SELECT
2    o.id AS order_id,
3    p.id AS payment_id,
4    p.created_at
5FROM orders AS o
6INNER JOIN (
7    SELECT order_id, MAX(created_at) AS latest_created_at
8    FROM payments
9    GROUP BY order_id
10) AS latest
11    ON latest.order_id = o.id
12INNER JOIN payments AS p
13    ON p.order_id = latest.order_id
14   AND p.created_at = latest.latest_created_at;

This works, but it may still return multiple rows if two payments share the same timestamp. If you truly need exactly one row, you still need a secondary tie-break rule.

Correlated Subquery Option

Another readable option is a correlated subquery that selects the child row id directly.

1SELECT
2    o.id AS order_id,
3    p.id AS payment_id,
4    p.created_at
5FROM orders AS o
6INNER JOIN payments AS p
7    ON p.id = (
8        SELECT p2.id
9        FROM payments AS p2
10        WHERE p2.order_id = o.id
11        ORDER BY p2.created_at DESC, p2.id DESC
12        LIMIT 1
13    );

This is often easy to understand because the business rule is visible in one place. Performance depends heavily on indexing and data volume.

Index for the Selection Rule

No matter which query form you choose, index the child table according to the join and ordering pattern.

CREATE INDEX idx_payments_order_created_id
    ON payments(order_id, created_at, id);

That index helps MySQL find the relevant child rows quickly and reduces the cost of ordering or scanning within each parent group.

Common Pitfalls

The most common mistake is trying to solve a one-to-many selection problem with DISTINCT. That may remove duplicate result rows, but it does not define which child row should win.

Another issue is saying "latest row" without a tie-breaker. If two rows share the same timestamp, the query may return more than one row or pick an arbitrary row depending on the pattern you used.

It is also easy to choose a SQL technique before deciding the business rule. The database cannot guess whether "one row" means newest payment, approved payment, primary address, or highest score.

Finally, do not ignore indexing. A correct query can still become painfully slow if the child table is large and there is no supporting index for the parent key and ordering columns.

Summary

• A plain INNER JOIN returns all matching child rows, not just one.
• Decide first which child row you want to keep and express that rule explicitly.
• In MySQL 8, ROW_NUMBER() is usually the clearest solution.
• In older MySQL versions, use aggregation or a correlated subquery with a deterministic tie-breaker.
• Add indexes that match the join key and row-selection order so the query scales.