I have a MySQL table (menu_items) of items used to build a menu. See data below. Each menu item is either a parent or a child. Items with a
parent_id of 0 is a parent. Items with a
'parent_id' > 0 is a child which belongs to the item whose ‘id’ corresponds to the ‘parent_id’ data.
See SQLFiddle here for schema and test data: http://sqlfiddle.com/#!9/8ec6bc/1
I am trying to create a query that gives me all 21 menu items, as well as the title of the parent menu item (if one exists). The parent menu title should be null if the item is itself the parent.
I have tried several different self joins like below, but am unable to get back the results I need. Any ideas?
SELECT mi1.*, mi2.title as parent_title FROM menu_items mi1 left join menu_items mi2 on mi2.parent_id = mi1.id;
You almost had it, flip the join relationship. Check if child.parent_id matches a parent.id.
select mi1.*, mip.title as parent_title from menu_items mi left join menu_items mip on mi.parent_id = mip.id;
parent_id not null but then setting those without parents to a magic number 0 is questionable. I’d suggest making
parent_id nullable and setting it to null for parents. Demonstration.
Answered By – Schwern
Answer Checked By – David Goodson (BugsFixing Volunteer)