How to join table with itself?

Question

I have this menu table, this table contain menu, and its parent.

The problem is i want to get how many child each menu have. Like this,

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Madhur Bhaiya, Sep 30 '18 at 06:24
Will there be multiple level child menu_id(s) ? If yes, by child, you mean all the children, grand child, grand grand child and so on.. ? — Madhur Bhaiya, Sep 30 '18 at 06:24
Please check the hierarchical recursive query link suggested. — Madhur Bhaiya, Sep 30 '18 at 06:30
@MadhurBhaiya in the link you gave me before its explain the parent, but what i need is to get how many child each menu have, pls kindly help me :") — Josh Parinussa, Sep 30 '18 at 12:18

LukStorms · Answer 1 · 2018-09-30T13:09:41.237

If you're just interested in how many child menus are directly under a main parent menu?

Then joining those with parent_id = 0 to the childmenus, and grouping on main menus should do.

For example:

SELECT MainMenu.menu_name AS Menu, COUNT(ChildMenu.menu_id) as Child
FROM YourMenuTable AS MainMenu
LEFT JOIN YourMenuTable AS ChildMenu ON ChildMenu.parent_id = MainMenu.menu_id
WHERE MainMenu.parent_id = 0
GROUP BY MainMenu.menu_id, MainMenu.menu_name
ORDER BY MainMenu.menu_name;

Test on Sql Fiddle here

How to join table with itself?

1 Answers1