SQL query help - creating menu

.NET
1

Hi Folks,

I’m trying to create a menu structure, subpages within pages, for use with my cms.

I have 3 tables in sql server, I would like to query all three tables and list those pages with a certain siteid, and subpages where the subpages.pageid matches the pages.pageid.

Could someone tell me how output the data using vb to create the menu below?

Something like this:

sitename

page
page
page
page
subpage
subpage
subpage
page
page

Here is my query so far:

SELECT * FROM sites
LEFT JOIN pages ON sites.siteid=pages.siteid
LEFT JOIN subpages ON pages.pageid=subpages.pageid
WHERE sites.siteID = 1ORDER BY sites.siteid, pages.pagesub ASC

These are my tables:

sites

siteID int Unchecked PK
siteName nvarchar(MAX) Checked
siteAdmin nvarchar(MAX) Checked
siteLive bit Checked

Pages

pageID int Unchecked PK
siteID int Checked FK linked to siteid in sites table
pageLink nvarchar(MAX) Checked
pageBody ntext Checked
pageSummary ntext Checked
pageTitle ntext Checked
pageOrder int Checked
pageHome bit Checked

subpages

subID int Unchecked PK
PageID int Checked FK linked to pageid in pages table
subBody ntext Checked
subSummary ntext Checked
subTitle nvarchar(MAX) Checked
subOrder nvarchar(MAX) Checked

2

your query looks okay, except for one thing – it uses the dreaded, evil “select star” and returns ~way~ more data than you need for a simple menu

like, say, the pageBody

imagine, retrieving the full contents of every page in the site just to print a menu

3

Thanks very much for your reply, do you know I can get the subpages underneath the parent page, so far I can just output using the reader.while()

While reader.Read()
mydata &= reader.Item(“sites.siteName”) & " " & " " & reader.Item(“pages.pagetitle”) & “<br>”
End while

4

sorry, can’t help you with the code, i was just responding to the sql question