here's what I am currently trying to achieve.
I have a users table which I want to join with itself.
So I query all users with no ParentUserId and also want an additional field called ChildrenCount where I can find a count of all users with the ParentUserId = UserId.
SELECT p.*, COUNT(*) as ChildrenCount FROM users p
LEFT JOIN users q
on q.userid = p.ParentUserId
WHERE p.ParentUserId = 0 AND p.CategoryId = 2 ORDER BY p.userid DESC LIMIT 10 OFFSET 0
If I understood you correctly, you can use a correlated query :
SELECT p.*, (SELECT count(*) FROM users s WHERE s.parentUserId = p.userid) as ChildrenCount FROM users p WHERE p.ParentUserId = 0 AND p.CategoryId = 2 ORDER BY p.userid DESC LIMIT 10 OFFSET 0