[SOLVED] Self join on column that may not have data

Issue

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;

Solution

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;

Demonstration.

Note: making 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)