Project Detail

Description

I have a tree menu on a site which is database driven. The key table (shopCategory) has cat_pk, category_text, parent_pk, url.

This tree menu has varying levels (Folder|product item or Folder|Folder|Folder|Item)

Linked to this tree menu are products from table (shopItems) and items are linked to the tree menu via TDCategoryID which links to cat_pk.

I have written an SQL Query that gives me back the nodes of the tree that have items linked to them.

What I now need is someone to write me an addition to this SQL Query to give me back the tree structure. By this I mean that the shopCategory table needs not only to contain the nodes that the shopItems link to but also all their parents however many there are.

I am told this may involve recursion, therefore requires stored routines but this is beyond my SQL skills.

I will provide an SQL dump of the two tables above and the SQL Query I have (very simple stuff) and I want either that Query modifying or a stored procedure writing that does the above and instructions as to how to install it on my server.

In addition I want the result set from the query inserting into a third table (shopCategoryLive) each time the query is run. This should also drop any records in that table before updating.