MySQL Join for parent record using multiple tables

Question

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 ?

Yea, it's right and works fine, but i'm asking if there is a more right or compact way to do the same query — Lwyrn
– Lwyrn, Commented Dec 14, 2012 at 19:01

Bohemian · Accepted Answer · 2012-12-14 19:07:56Z

1

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

answered Dec 14, 2012 at 19:07

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join for parent record using multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related