i have a website that has main sections and subsections and i want to create a hieracial site map e.g.
main
sub section 1
subsection 1 page1
subsection 1 page 2
sub section 2
subsection 2 page1
subsection 2 page 2

in my database i have 3 tables .. 1st is called tablemain which contains 2 fields the section name and the section id.. 2nd table is called tablesub which which also contains 2 fields a section name and id .. the 2rd table is called pages and contains 3 fields a maincatid a subcatid and pagecontent field.

my question is how would i query these tables and loop through to create a hieracial structure for a site map

any help / suggestions with the logic of it much appreciated as at a dead end

Personally I would use just 1 table, with 3 fields - ID, name and parent ID - then you can build up a tree menu with as many nested layers of folders as you want,

However, if you wish to keep your current structure, then you will need to create a single SQL query which links the 3 tables together - something like

SQL = 'select * from tablemain, tablesub, pages where subcatid = subsection_id and subsection_parent = main_id'

this is assuming the tables are of the following structure:

tablemain table
main_id (autonumber)
main_name (text)

tablesub table
subsection_id (autonumber)
subsection_name (text)
subsection_parent (number - linked to main_id)

pages table
page_id (autonumber)
page_name (text)
subcatid (number - linked to subsection_id)
maincatid (number linked to main_id)
page_link (text - url of page)



then do a loop through which is something like this



set rs = conn.execute(SQL)
if not rs.eof then
lastSection = 0
lastSub = 0
while not rs.eof

' if new main section, print name out
if lastSection <> rs.fields("main_id" then
response.write rs.fields("main_name" & "<br>"
end if

' if new subsection, print name out
if lastSub <> rs.fields("subsection_id" then
response.write rs.fields("subsection_name" & "<br>"
end if

' then print name out of pages
response.write rs.fields("subsection_name" & " " & rs.fields("page_name" & "<br>"

'then set lastSection and lastSub to current section ids
lastSection = rs.fields("main_id"
lastSub = rs.fields("subsection_id"

rs.movenext
wend
end if


You'll have to replace some the field names for the ones in your database.

I think this should do the trick for you.

cheers for the response it was a while ago and i did manage to figure it out in a similar way to what you suggeted.. one quick question :
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Personally I would use just 1 table, with 3 fields - ID, name and parent ID - then you can build up a tree menu with as many nested layers of folders as you want,
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

can you explain this a bit more maybee my logic in doing multi level pages is wrong