How to retrieve data using multiple join case

[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.