Self Join in mySql

I have a scenario where a table is required to be joined with itself. This process is called self-join.

Say, We have a table called 'category' with the following column structure.

cat_id	cat_name	parent_id
1	parent1	0
2	parent2	0
3	child1	1
4	child2	2
5	sub-child1	3
6	sub-child2	4

The table above has a parent-child relationship among rows. For example, category "parent1" has a child "child1" and again "child1" has a child named "sub-child1".

The table might have more such entries i.e "sub-child1" can have multiple children under it and even those children can have n number of children added under it. 

Problem is, how to get the tree for top-most category "parent1" ?

We need to use self-join techniques to solve this issue. Below is the SQL which will fetch all the children of "parent1".

SELECT t.cat_name as cat1, m.cat_name as cat2, l.cat_name as cat3 FROM category t

left join (select * from category ) as m on t.cat_id = m.parent_id

left join (select * from category ) as l on m.cat_id = l.parent_id

where t.cat_name = 'parent1';

Here, we have left joined the category table with itself twice as we wanted to find out 3rd level children. Using only "join" would not do the proper searching, because that will miss out those second level category names which don't have any children under it. Plain "join" returns data only if there is a match where as "Left Join" would return parent category name even if there is no match for any children.

If we were to search for only 1 level of children, we would have used the following query :

SELECT t.cat_name as cat1, m.cat_name as cat2 FROM category t

left join (select * from category ) as m on t.cat_id = m.parent_id where t.cat_name = 'parent1';

To find all the category names which have immediate children and each of those children has atleast one sub-children under it we can use the following SQL :


SELECT t.cat_name as cat1, m.cat_name as cat2, l.cat_name as cat3 FROM family as t, family as m, family as l 
where t.cat_id = m.parent_id and  m.cat_id = l.parent_id and t.cat_name = 'parent1'