How to select a limited amount of rows for each foreign key? (and how to specify the relatioship)

Question

Hi, recently I have posted this question and now I want the same query but with one relationship.

Now I have the "feeds" table and the "feed_entries" table. I want a limited amount of rows per category. The query above make use of one table with category_id field:

SELECT x.* FROM (SELECT t.*, 
CASE WHEN @cat != t.category_id THEN @rownum := 1 ELSE @rownum := @rownum + 1 END AS rank, 
@cat := t.category_id 
FROM feed_entries t 
JOIN (SELECT @rownum := NULL, @cat := 0) r 
ORDER BY t.category_id DESC) x 
WHERE x.rank <= 3 AND x.deleted =0 ORDER BY x.category_id, x.date DESC LIMIT 50

But instead of t.category_id I need to use f.category_id from the "feeds" table. How can I specify the relationship in this query?

Answer 1

Got it. Actually this sql was the first I have tested, but an error has ocurred because I forgot the comma before the CASE clausule.

The correct is:

SELECT x . *
FROM (
SELECT t . * , f.categoria_id AS idCategoria,
CASE WHEN @cat != f.categoria_id
THEN @rownum :=1
ELSE @rownum := @rownum +1
END AS rank, @cat := f.categoria_id AS catteste
FROM feed_entries t, feeds f
JOIN (
SELECT @rownum := NULL , @cat :=0
)r
WHERE t.feed_id = f.id
ORDER BY f.categoria_id DESC
)x
WHERE x.rank <=3
AND x.deleted =0
ORDER BY x.categoria_id, x.date DESC
LIMIT 50