How to Join a MySQL Table to Only the Most Recent Row in a Related Table?

MySQL: Joining to Only the Most Recent Row

In this scenario, you have tables customer and customer_data, where customer_data contains a history of changes for each customer. Your goal is to retrieve customer information while joining to only the most recent row in customer_data.

To achieve this, utilize a subquery within the WHERE clause to identify the maximum ID for each customer in customer_data. Join this subquery to the customer table using the LEFT JOIN syntax, as shown below:

SELECT c.*,
FROM customer AS c
LEFT JOIN customer_data AS d ON d.customer_id = c.customer_id AND d.ID = (
  SELECT MAX(ID)
  FROM customer_data
  WHERE customer_id = c.customer_id
)
WHERE name LIKE '%Smith%'
LIMIT 10, 20;

In this query:

• LEFT JOIN ensures that all rows from the customer table are included in the result, even if they do not have a corresponding row in customer_data.
• The subquery within the WHERE clause calculates the maximum ID for each customer in customer_data.
• The result of the subquery is then compared to the ID column in customer_data to select only the most recent row.

This approach effectively isolates the most recent entries for each customer and joins them to the customer table for further processing.

The above is the detailed content of How to Join a MySQL Table to Only the Most Recent Row in a Related Table?. For more information, please follow other related articles on the PHP Chinese website!