SQL Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Group by

Author

Topic

matthisco
Starting Member

48 Posts

Posted - 2010-01-27 : 05:14:54

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.

Could someone tell me how to use the GROUP BY Statement 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

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 05:33:39

Change

LEFT JOIN subpages ON pages.pageid=subpages.pageid

to be an INNER JOIN?

That will only include rows where that condition is True.

matthisco
Starting Member

48 Posts

Posted - 2010-01-27 : 06:27:23

Thanks for your reply.

The query works, but the subpages appear at the end of the list still. When I would ike them to appear uner the appropriate page.

Can this be done SQL?

I'm outputting the result using vb.net:

While reader.Read()
mydata &= reader.Item("sitename") & " " & reader.Item("pageid") & " " & reader.Item("pagetitle") & "<br>"

If not IsDBNull(reader.Item("pagesub")) then
mydata &= " " & reader.Item("subtitle")
end if
End while

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 07:10:03

Yup, I reckon you just need to adjust your ORDER BY clause.

My guess is this, but you will know the columns better than I can guess!:


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

matthisco
Starting Member

48 Posts

Posted - 2010-01-28 : 09:09:49

Thanks again for you reply.

I've changed tha names of the fields to make them all unique.

My query is below:

SELECT subpages.subpageid, pages.pageid, sites.sitename, pages.siteid, pages.pagetitle
FROM sites
LEFT JOIN pages ON pages.siteid = sites.siteid
LEFT JOIN subpages ON pages.pageid=subpages.subpageid WHERE sites.siteID = 1
ORDER BY sites.siteid, pages.pageid, subpages.subpageid ASC

It returns all the rows, but misses out the row that contains pages.pageid = 4, can I alter my query to get the pageid?

see below:

subpageid | pageid | sitename | siteid | pagetitle

NULL 1 SCHS 1 SCHS
NULL 2 SCHS 1 About Us
NULL 3 SCHS 1 Your Services
4 4 SCHS 1 Equality and Diversity Team at SCHS
4 4 SCHS 1 Equality and Diversity Team at SCHS
4 4 SCHS 1 Equality and Diversity Team at SCHS
4 4 SCHS 1 Equality and Diversity Team at SCHS
NULL 5 SCHS 1 Single Equality Scheme
NULL 6 SCHS 1 Diversity Strands
NULL 7 SCHS 1 Equality Impact Assessments
NULL 8 SCHS 1 Quality and Safety Committee
NULL 9 SCHS 1 Contact Us

Misses out this row:

NULL 4 SCHS 1 E & Q

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:31:22

"misses out the row that contains pages.pageid = 4,"

I've read your code carefullyA, but maybe I missed something. At my reading this is not possible, unless the PAGES record does NOT have SITEID=1

Whate does:


SELECT pages.pageid, pages.siteid, pages.pagetitle
FROM pages 
WHERE pages.siteID = 1  AND pages.pageid = 4

return?

Whatever it returns that must be included in your earlier query which basically just JOINs [sites] table with WHERE sites.siteID = 1

matthisco
Starting Member

48 Posts

Posted - 2010-01-28 : 10:20:24

HI Kirsten,

Thats my bad, the query works great!

I hadn't selected the right fields.

Thanks again for your help

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 10:25:24

no problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources