Search code examples
mysqljoinleft-joinparent

MySQL Join for parent record using multiple tables

I've three tables: menu, menu_data, languages . The first one is used to store the menu information that not needs to be localized, the second contains localized data based on language ( like title ) and the last one contains the language IDs. The menu can be owned by a parent and i want to extract this parent when selecting the child menu. I can do it using a nested LEFT JOIN query like this:

SELECT la.title, la.permaname, menu.edittime, menu.id, la2.title AS parent_title, pmenu.id AS parent_id 
FROM fm_menu AS menu 
LEFT JOIN fm_menu_data AS la ON ( menu.id = la.targetid and la.languageid = 1 ) 
LEFT JOIN fm_menu AS pmenu ON pmenu.id = menu.parentmenu 
LEFT JOIN fm_menu_data AS la2 ON ( pmenu.id = la2.targetid and la2.languageid = 1 )

But, my question is, there a more compact, faster or correct way to do something like this ?

Solution

  • Your query looks good.

    You can make it slightly more compact by removing the unnecessary brackets from your join conditions and removing the optional AS keywords.

    This is the same, but expressed in less characters:

    SELECT la.title, la.permaname, menu.edittime, menu.id, la2.title AS parent_title, pmenu.id AS parent_id 
FROM fm_menu AS menu 
LEFT JOIN fm_menu_data la ON menu.id = la.targetid and la.languageid = 1
LEFT JOIN fm_menu pmenu ON pmenu.id = menu.parentmenu 
LEFT JOIN fm_menu_data la2 ON pmenu.id = la2.targetid and la2.languageid = 1