multiple result from same table

[anand01]

Hi All ,
I need to get multiple result from same table
my table structure is

id  category    parentid
1   dress         0
2   men           1
3   mtshirt       2
4   women         1
5   wtshirt       4

If I give wtshirt it should produe result as wtshirt-men-derss
how can I write query for this could any one help me
thanks in advance

[Javvy]

The most basic form would be..

SELECT * FROM <table name> WHERE category = 'wtshirt';

[anand01]

hi Javvy,
thanks for your reply
but my question is if i give category as wtshirt then the result should be like this **wtshirt-women-derss **(i.e wtshirt's parent id is 4 so it should women ,again wemon's parent is 1 so it should select dress)

[smantscheff]

You're trying to get a partial tree as a result. In this setup, you will need a (recursive) (user defined) function which walks all the way up from wtshirt to dress.

[anand01]

thanks for your replay smantscheff,
Could you show some samples or tutorilas related this

[urtrivedi]

I had same issue, to avoid recursion i stored all paraent level relation in another table called item_level like for
child_id, parent_id, level
4, 4, 0 (self record at level 0)
4, 1, 1 (first parent that is women for wtshrit at level 1)
4, 0, 2 (second parent (grand father) that is dress for wtshrit at level 2)

same you have to add all upper parents for all ids in your main table

so whenver record is inserted in main table you can insert multiple parent records in level table like abvoe

then you can write join query to sum up things main table to level table

There is an alternative to the parent_id method, but might not be approapriate for you:

MODIFIED PREORDER TREE TRAVERSAL

http://www.sitepoint.com/hierarchical-data-database-2/

[anand01]

thanks for your replies urtrivedi and cdiafol