How to retrieve data using multiple join case

[eluser]ashutosh[/eluser]

Currently i have foreign key thirdsmcontent_id value , now i want to get the value of

`thirdsubmenu_name` from `thirdmenu` table,`submenu_name` from `submenu` table,`menu_name` from `mainmenu` table .

Please help me to solve this problem.
I tried below code but my model code is not correct. it is not display my data

Code:

1)Table: mainmenu
        ---------------
         mainmenu_id   PK(primary key)
         menu_name     .....

        2)Table: submenu
        -------------------
         submenu_id     PK
         mainmenu_id    FK (foreign key refrences mainmenu table)
         submenu_name   .....


        3)Table: thirdsubmenu
        --------------------
          thirdsubmenu_id     PK
          submenu_id          FK (foreign key refrences submenu table)
          thirdsubmenu_name     ........


        4)Table: thirdsmcontentdetails
        --------------------
          thirdsmcontent_id   PK
          thirdsubmenu_id     FK (foreign key refrences thirdsubmenu table)
          content              ......

In My controller

Code:

$thirdsubmenu_id = $this->uri->segment(4);
              
       $data['main_menuname'] = $this->thirdsmcontentdetailsmodel->getMainMenuNameOfSubmenu($thirdsubmenu_id);
       $data['sub_main_menuname'] = $this->thirdsmcontentdetailsmodel->getSubMenuNameSubmenu($thirdsubmenu_id);
      $data['third_sub_main_menuname'] = $this->thirdsmcontentdetailsmodel->getThirdSubMenuNameSubmenu($thirdsubmenu_id);

In My model

Code:

//---------------------------get Main Menu Name by Menu id-----------------------------------
     function getMainMenuNameOfSubmenu($thirdsubmenu_id)
      {    
             $this->load->database();
             $query = $this->db->join('thirdsubmenu','thirdsubmenu.submenu_id = thirdsmcontentdetails.submenu_id')->get_where('thirdsubmenu',array('thirdsubmenu_id'=>$thirdsubmenu_id));  
             return $query->row('menu_name');
      }
        //---------------------------get Sub  Menu Name by Menu id-----------------------------------
     function getSubMenuNameSubmenu($thirdsubmenu_id)
      {    
             $this->load->database();
             $query = $this->db->join('mainmenu', 'mainmenu.mainmenu_id = submenu.mainmenu_id')->get_where('thirdsubmenu',array('thirdsubmenu_id'=>$thirdsubmenu_id));  
             return $query->row('submenu_name');
        }
        //---------------------------Get the third menu name-------------------------------------
         function getThirdSubMenuNameSubmenu($thirdsubmenu_id)
      {    
             $this->load->database();
             $query = $this->db->get_where('thirdsubmenu',array('thirdsubmenu_id'=>$thirdsubmenu_id));  
             return $query->row('thirdsubmenu_name');
        }

Getting Error:

Code:

A Database Error Occurred

        Error Number: 1066

        Not unique table/alias: 'thirdsubmenu'

        SELECT * FROM (`thirdsubmenu`) JOIN `thirdsubmenu` ON `thirdsubmenu`.`submenu_id` = `thirdsmcontentdetails`.`submenu_id` WHERE `thirdsubmenu_id` = '1'

        Filename: D:\xampp\htdocs\system\database\DB_driver.php

        Line Number: 330

[eluser]TheFuzzy0ne[/eluser]
You're joining thirdsubmenu onto itself. If you're going to do that, you need to give it an alias.

[eluser]ashutosh[/eluser]
My model code is wrong.

This is my first codeigniter program where i am using join.

if you could answer a solution for my above doubt then it will be very helpful for me . I can refer your code for my future projects. Please provide a solution with code.

[eluser]TheFuzzy0ne[/eluser]
That's easier said than done. I think your database structure is also wrong. You could easily do that with a single table, and add a parent_id field, so you can recursively join the table on itself. However, that would mean querying the database in a loop, which, whilst it works, I wouldn't recommend. There may be a way to do it with pure SQL, but I'm not sure.

Another way to approach this, would be with using a [url="http://en.wikipedia.org/wiki/Tree_traversal"]binary tree[/url]. That should be able to accommodate as many levels of menu as you might need. It takes a little overhead to maintain, but it should work. Again, you'd only need a single table.

Here's a library I've used in the past. It's no longer maintained, but it should still work. https://github.com/EllisLab/CodeIgniter/wiki/Trees.

There may be other solutions for this too, but nothing springs to mind. I'll post back if anything does.

[eluser]ashutosh[/eluser]
You said that "You could easily do that with a single table, and add a parent_id field, so you can recursively join the table on itself." . I like this point. i did not understood it totally. Could you please describe it in details, with table structure.

[eluser]TheFuzzy0ne[/eluser]
This might help you: http://stackoverflow.com/questions/52133...-structure

[eluser]ashutosh[/eluser]
That link is good. I learn that technique(http://mikehillyer.com/articles/managing...-in-mysql/). For the next project hereafter,i will implement as you suggested.

But for the above question i already written lots of code. Only the remaining part is a doubt which i asked in the this thread. Can you just post a answer code for my above question, please. It will be very helpful for now.