SQL Query - to filter parent/child tree table

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.

Skills: Data Processing, Linux, PHP, Website Design

See more: sql tree query, sql query tree, parent child sql query, sql query parent child tree, sql parent child tree, sql tree table, mysql tree query, sql query tree structure, sql parent tree, sql tree parent child, tree sql query, filter tree sql, sql tree filter, parent child tree sql query, tree menu sql query, parent child tree sql, sql query parent child, mysql query parent child, sql query tree parent, sql menu tree, tree query sql, parent child sql query mysql, sql parentchild tree query, sql menu parent, parent sql menu

About the Employer:
( 11 reviews ) Hull, United Kingdom

Project ID: #238575

Awarded to:


Hi, please check PM, thanks.

$30 USD in 1 day
(253 Reviews)

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.

$50 USD in 1 day
(130 Reviews)

Hi, can do it. Recursion is needed or View structure if the MySQL version is 5.

$50 USD in 1 day
(131 Reviews)

Please check it out the PM for more info, thanks…

$30 USD in 1 day
(100 Reviews)

easy work.

$50 USD in 0 days
(87 Reviews)

Hi, i have long experience in SQL , TSQL and PLSQL i have handled similar tasks plz check PM thanks, Hussien Sharaf

$80 USD in 2 days
(15 Reviews)

Please check PM, Thanks.

$80 USD in 2 days
(19 Reviews)

i can do it

$30 USD in 1 day
(0 Reviews)

Hi, see PM please.

$50 USD in 2 days
(0 Reviews)