SQL Query - to filter parent/child tree table

This project was successfully completed by SoftEngineer for $30 USD in a day.

Get free quotes for a project like this
Project Budget
$30 - $250 USD
Completed In
1 day
Total Bids
Project 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.

Completed by:

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online