How to Retrieve One Product per Category Using JOIN with LIMIT 1?

Joining Tables with LIMIT 1 on the Joined Table

The task is to join two tables but retrieve only one record from the joined table per record in the first table. Consider the following tables:

categories (id, title)
products (id, category_id, title)

A simple query would join these tables as follows:

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

However, this returns multiple rows for each category, which is undesirable. We need to limit the results to one record from the products table per category.

One approach recommended in a similar question is to use a subquery to retrieve the primary key of the desired record:

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
 )

This query efficiently retrieves the desired results without encountering nested query issues. Additionally, it outperforms other proposed solutions in terms of execution time, particularly when dealing with large datasets.

The above is the detailed content of How to Retrieve One Product per Category Using JOIN with LIMIT 1?. For more information, please follow other related articles on the PHP Chinese website!