Self Join On Column That May Not Have Data
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.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key