1

Thank you in advance for your help...

I want to get the parent site list/informatoin for a site based on its ID. There is a site hierarchy described in 2 tables, like this:

table: site
- id  (PK)
- name

table: hierarchy
- siteid   (PK) (siteid   => site.id)
- parented (PK) (parented => site.id)
- topid         (topid    => site.id)

For example, you may have the following data in the tables:

site:
id,name
0,Earth
1,US
2,NY
3,GA
4,Queens
5,Farmers' Market
6,Buckhead

hierarchy:
siteid,parented,topid
5,4,1
4,2,1
2,1,1
1,0,1
5,6,1
6,3,1
3,1,1
1,0,1

I want to get all the parents of the Farmers's Market sites. There are markets in both Buckhead and Queens.

How do I make SQL walk / recurse up the tree to get something like this if I want the parent list for site id=5?:

site_name,parent_name
Farmers' Market,Queens
Queens,NY
NY,Earth
Farmers' Market,Buckhead
Buckhead,GA
GA,Earth

It occurs to me that there may need to be more than one row in the site table for Farmers' Market, but, I don't think so...

Thanks for your help,

David

user3220745
  • 63
  • 6
  • possible duplicate of [Connect By Prior Equivalent for MySQL](http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql) – mvp Aug 30 '14 at 21:02
  • Add a herarcy-level column to you hierarchy table. Then join the hierarchy-table hierarchy-level times to itself and vary the join key. – Benvorth Sep 25 '14 at 11:59

0 Answers0