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.
9 freelancers are bidding on average $50 for this job
hace omputer since bahalavr degree, exp hi level of sql queries and stored procedures. if it is possible to do with sql i will [url removed, login to view] not - will do with procedure.