SQL query help

Report · Jan 26, 2010

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 subid is the pageid.

Something like this:

sitename

page

subpage

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

Report · Jan 26, 2010

Other than the fact that you are selecting * instead of just the fields you need, and that left joins might not be necessary, your query looks fine. The group attribute of cfoutput will probably help you achieve your desired result.

Report · Jan 26, 2010

Thanks Dan, I'll keep having a play