How Can I Limit Join Results to a Single Record per Row from the Joined Table?

LIMIT JOIN: Retrieving Single Records from Joined Tables

When joining two tables, it can be necessary to limit the results to a single record per row from the joined table. This can be achieved in MySQL using the LIMIT 1 clause on the subquery referenced by the join clause.

Consider the scenario where we want to join the categories and products tables to obtain category information and the first product associated with each category.

SELECT c.id, c.title, p.id AS product_id, p.title
FROM categories AS c
JOIN products AS p ON c.id = p.category_id

This query would return all product records for each category, which is not the desired output. To limit the results to the first product for each category, we can modify the query as follows:

SELECT
 c.id,
 c.title,
 p.id AS product_id,
 p.title AS product_title
FROM categories AS c
JOIN products AS p ON
 p.id = (
  SELECT p1.id FROM products AS p1
  WHERE c.id=p1.category_id
  ORDER BY p1.id LIMIT 1
 )

In this modified query, a subquery is used to retrieve the id of the first product for each category. This id is then used in the join clause to limit the results to the desired single record.

This approach is particularly efficient in scenarios with a large number of products, as it avoids the performance overhead of aggregating multiple product records for each category. Additionally, it is applicable in both MySQL and PostgreSQL, providing a versatile solution for data retrieval.

The above is the detailed content of How Can I Limit Join Results to a Single Record per Row from the Joined Table?. For more information, please follow other related articles on the PHP Chinese website!