M. Alim M. Alim - 1 month ago 7
MySQL Question

three time join query in same table

i have a one mysql table as

MENU_ID MENU_NAME PARENT_ONE PARENT_TWO
1 home 0 0
2 home_sub 1 0
3 third_home 1 2


i want the name against parent one and parent twos id's with each row if have.
i have tried something but its giving only one that has parent one and two both values like

MENU_ID MENU_NAME PARENT_ONE PARENT_TWO MENU_NAME MENU_NAME
3 third_home 1 2 home home_sub


but i want

MENU_ID MENU_NAME PARENT_ONE PARENT_TWO MENU_NAME MENU_NAME
1 home 0 0
2 home_sub 1 0 home
3 third_home 1 2 home home_sub



this the query that i tried

SELECT a.*, b.MENU_NAME, c.MENU_NAME
FROM menu_table a JOIN menu_table b ON a.PARENT_ONE = b.MENU_ID
JOIN menu_table c ON a.PARENT_TWO = c.MENU_ID ORDER BY a.MENU_ID;


advanced thank's if anyone can help me for this !

Answer

You should use left join (join is alias for inner join)

SELECT a.*, b.MENU_NAME, c.MENU_NAME 
FROM menu_table a 
LEFT JOIN menu_table b ON a.PARENT_ONE = b.MENU_ID 
LEFT JOIN menu_table c ON a.PARENT_TWO = c.MENU_ID 
ORDER BY a.MENU_ID;