John Davidson John Davidson - 5 months ago 17
MySQL Question

MySQL JOIN with LIMIT 1 on joined table

I want to join two tables, but only get 1 record of table2 per record on table1

For example:

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 would get me all records in
products
, which is not what I want. I want 1 [the first] product per category (I have a
sort
column in the products field).

How do I go about doing that?

Answer

I would try something like this:

SELECT C.*,
      (SELECT P.id, P.title 
       FROM products as P
       WHERE P.category_id = C.id
       LIMIT 1)
FROM categories C