LIMIT on a JOIN (can't work it out!)

[gausie]

SELECT E1.name AS name, E1.id AS id, E2.name AS parentname FROM categories AS E1 INNER JOIN categories AS E2 ON E1.parent_id = E2.id WHERE E2.parent_id = '0' ORDER BY E1.amount ASC

 

So I want to limit the results from E1 (for each E2 record) to 5 only

 

How can I do that?

 

Gausie

[cooldude832]

you might need to query inside for

select count(E1.id) from `E1` where E1.id=E2.id

and use that as your Limit

[fenway]

You can't limit on a join... you'd have to create a derived table with this artificial limit, then join it back.