Angelika S Michel Angelika S Michel - 5 months ago 19
SQL Question

INNER JOIN COUNT on same table

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


I am not getting any error but the query isn't working as I expected. I do not get the count of all related children.
Database is MYSQL.

Answer

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