CODEWITHSUNDEEP
mysqlsql
Smith
Smith

Reputation: 5959

Get familiy tree of child category

I have a table in which i store a tree structure. Basically the family structure is 

Category > Sub category > sub sub category

The parent here is the one with parent_id of 0. I need to get the family structure of any category through sql. The table looks like

cat_id | parent_id | name
-------------------------
1      |    0      | a
2      |    0      | b
3      |    1      | c
4      |    2      | d
5      |    3      | e
6      |    4      | f

so for the cat_id of 4, the the result i need is

c1.cat_id | c.name | c2.cat_id  |  c2.name   | c3.cat_id |   c3.name
2              b        4            d           null          null

so for the cat_id of 6, the the result i need is

c1.cat_id | c.name | c2.cat_id  |  c2.name   | c3.cat_id |   c3.name
2              b        4            d           6             f

This is the code am currently using,which gives more than one row for each result

SELECT c1.name cat1, c1.cat_id cat1_id,c2.name cat2, c2.cat_id cat2_id ,c3.name cat3, c3.cat_id cat3_id
 FROM ea_category AS c1
  JOIN ea_category AS c2 ON (c2.parent_id = c1.cat_id)
  JOIN ea_category AS c3 ON (c3.parent_id = 0)
where c1.cat_id = 4

What am i doing wrong

Upvotes: 0

Views: 50

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

Create a function to retrieve parent id 

CREATE FUNCTION fn_getparentCatId

   ( id INT ) RETURNS INT
BEGIN 
 DECLARE parent_catid INT; 
 SET parent_catid = (SELECT parent_id FROM categoryTable WHERE cat_id=id);
 RETURN parent_catid;
END

Create another function for parent category name

CREATE FUNCTION fn_getCatName

   ( id INT ) RETURNS VARCHAR(10)
BEGIN 
 DECLARE categoryName Varchar(10); 
 SET categoryName  = (SELECT name FROM categoryTable WHERE cat_id=id);
 RETURN categoryName;
END

Then use it like below

SELECT cat_id, name 
       ,fn_getparentCatId(cat_id) , fn_getCatName(fn_getparentCatId(cat_id))
       ,fn_getparentCatId(fn_getparentCatId(cat_id)) , fn_getCatName(fn_getparentCatId(fn_getparentCatId(cat_id)))

FROM categoryTable
WHERE cat_id=6

Upvotes: 1

Related Questions