MySQL JOIN with LIMIT 1 on joined table
Solution 1:
I like more another approach described in a similar question: https://stackoverflow.com/a/11885521/2215679
This approach is better especially in case if you need to show more than one field in SELECT. To avoid Error Code: 1241. Operand should contain 1 column(s)
or double sub-select for each column.
For your situation the Query should looks like:
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 = ( --- the PRIMARY KEY
SELECT p1.id FROM products AS p1
WHERE c.id=p1.category_id
ORDER BY p1.id LIMIT 1
)
Solution 2:
Accepted answer by @goggin13 looks wrong. Other solutions provided to-date will work, but suffer from the n+1 problem and as such, suffer a performance hit.
n+1 problem: If there are 100 categories, then we would have to do 1 select to get the categories, then for each of the 100 categories returned, we would need to do a select to get the products in that category. So 101 SELECT queries would be performed.
My alternative solution solves the n+1 problem and consequently should be significantly more performant as only 2 selects are being performed.
SELECT
*
FROM
(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
ORDER BY c.id ASC) AS a
GROUP BY id;
Solution 3:
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
GROUP BY c.id
This will return the first data in products (equals limit 1)
Solution 4:
What about this?
SELECT c.id, c.title, (SELECT id from products AS p
WHERE c.id = p.category_id
ORDER BY ...
LIMIT 1)
FROM categories AS c;