1

I am building an e-commerce website in which category management plays an important role. The table structure for category is given below

category_id  |  category_name   |   parent_category_id 
------------------------------------------------------  
1            |  Shoes           |   NULL
2            |  Sports Shoes    |   1
3            |  Fila Shoes      |   2
4            |  Black Shoes     |   3

parent_category_id is a foreign key reference to category_id

I want to backtrack from the last child to ancestors. I mean like this

Black Shoes-->Fila Shoes -->sports shoes --> shoes

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
vinoth
  • 51
  • 1
  • 5
  • You should search for "mysql tree structure", there are a lot of similar questions… – feeela Oct 21 '13 at 11:21
  • 1
    Possible a duplicate http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way – Sashi Kant Oct 21 '13 at 11:22

1 Answers1

0

Using this design, there is no way to do this with only one query, unless you know the exact number of nodes you will need to select (in which case, a simple JOIN does the trick).

You can execute one query for each node: SELECT parent_category_id, category_name WHERE category_id = ;

If you have many nodes and execute this operation very often, you could even consider doing this with a stored procedure which returns many resultsets.

Of course things are much simpler in standard SQL, but MySQL does not support recursive queries.

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
  • Thanks for spending valuable time to answer. But whatever you said I know it earlier I have good knowledge in JOIN. The thing is I might not know the depth of the child. – vinoth Oct 22 '13 at 04:16
  • I'm sure you know JOIN. If you really want to use only one command (and not using a stored procedure) I suggest you use a little trick: just add a column which says the current row's depth (the dinstance between current row and the root). The program which reads the rows can then compose the proper JOIN statement. But frankly, I wouldn't do so: I would just use multiple JOINs until I reach the root. – Federico Razzoli Oct 23 '13 at 07:43