MySQL Forums
Forum List  »  MySQL Query Browser

Help with an sql query
Posted by: akis papadopoulos
Date: December 22, 2011 06:30AM

Hello all
I have a little query problem.
I am trying to get category path from a product in virtuemart
I have 4 tables jos_vm_product, jos_vm_product_xref, jos_vm_category and jos_vm_category_xref
I have the product_id which is in jos_vm_product . When I have the product_id we link it in jos_vm_product_xref with category_id
And then having product_id we get the name from jos_vm_category.
The problem is that I need the full path which is inside jos_vm_category_xref.
To get the full path we have to know the category_id from product_id(which I have it)and then we check if the category_id = category_child_id then it is child and we need to get and the category_parent_id until category_parent_id=0 which means that it is the root.

we have the category_id=3 from product_id=1
The name from category_id=3 (jos_vm_category) is sub sub category
in jos_vm_category_xref we have category_child_id=3 and parent_id =2
so we check below that category_child_id=2 and parent_id=1
and below category_child=1 and parent_id=0 so we are in root category now.
and for all those we have our root and the output is
sub sub category(name from child_parent_id=3) / sub category (name from parent_child_id=2) / category (name from parent_child_id=1) / root (parent_id =0)

I hope someone understands what I need and have a clue how to do it .
Thanks in advance.

Options: ReplyQuote

Written By
Help with an sql query
December 22, 2011 06:30AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.