How to Retrieve the First Product for Each Category Using MySQL JOIN and LIMIT 1?

MySQL JOIN with LIMIT 1 on Joined Table

This question revolves around joining two tables while limiting the results from the joined table to a single record for each match in the first table.

In this instance, consider two tables: categories and products, where each product belongs to a category. The objective is to retrieve each category along with the first product within that category.

To accomplish this, a subquery is employed to fetch the primary key of the first product within each category sorted by id. This primary key is then used in the outer query to retrieve the corresponding category and product information.

The query can be structured 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
  )

This approach ensures that only a single product record is retrieved per category, effectively addressing the desired result.

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