Mysql Multiple Joins to portray a table layout of my categories

I'm trying to portray a list of categories, the sub and sub and so on like this.

Root             Sub Level 1
Personal income  Adelaide IT Solutions (AITS)
Personal income  DJ
Personal income  Ebay Sales
Personal income  IAG
Rental income    Cash Grove Pasadena
Rental income    Winston Ave Cumberland Park
Personal income  Misc Income

I've written the following SQL statement which returns results, but only for when it matches the join, for example, if I 开发者_如何转开发have a join going 3 levels deep, it will only list the categories to which has a category that deep.

I'm assuming I have to use nested select's or something similar but have no idea how to go about it.

SELECT
    c.name as 'Root',
    s.name as 'Sub Level 1',
    s2.name as 'Sub Level 2',
    s3.name as 'Sub Level 3'
FROM catergories c
    INNER JOIN catergories s ON c.id = s.parent
    INNER JOIN catergories s2 ON s.id = s2.parent
    INNER JOIN catergories s3 ON s2.id = s3.parent

Any help would be greatly appreciated.

Fairly simple non recursive adjacency list stored procedure implementation which allows you to make a single call from your application layer to the DB and retrieve the required hierarchy.

mysql> call employees_hier(1);
+--------+-----------------+-------------+-----------------+-------+
| emp_id | emp_name        | boss_emp_id | boss_name       | depth |
+--------+-----------------+-------------+-----------------+-------+
|      1 | f00             |        NULL | NULL            |     0 |
|      2 | ali later       |           1 | f00             |     1 |
|      3 | megan fox       |           1 | f00             |     1 |
|      4 | jessica alba    |           3 | megan fox       |     2 |
|      5 | eva longoria    |           3 | megan fox       |     2 |
|      6 | keira knightley |           5 | eva longoria    |     3 |
|      7 | liv tyler       |           6 | keira knightley |     4 |
|      8 | sophie marceau  |           6 | keira knightley |     4 |
+--------+-----------------+-------------+-----------------+-------+
8 rows in set (0.00 sec)

Obviously replace employees with your categories...

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',6);


drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);

Assuming the maximum is 3 levels deep, the simplest implementation would be using a LEFT JOIN to show all categories in the Root column, and their subcategories if they exist:

SELECT
 c.name as 'Root',
 s.name as 'Sub Level 1',
 s2.name as 'Sub Level 2',
 s3.name as 'Sub Level 3'
FROM catergories c
 LEFT JOIN catergories s ON c.id = s.parent
 LEFT JOIN catergories s2 ON s.id = s2.parent
 LEFT JOIN catergories s3 ON s2.id = s3.parent

With the query above you will have many duplicates - ALL categories will show as Root, and some of them will show again as sub categories in various levels. To have only Parents as root, I guess you can add the rule:

SELECT
 c.name as 'Root',
 s.name as 'Sub Level 1',
 s2.name as 'Sub Level 2',
 s3.name as 'Sub Level 3'
FROM catergories c
 LEFT JOIN catergories s ON c.id = s.parent
 LEFT JOIN catergories s2 ON s.id = s2.parent
 LEFT JOIN catergories s3 ON s2.id = s3.parent
WHERE c.parent IS NULL  
-- or parent=-1, or whatever a category with no parent has for in the parent column