Help with an SQL view problem

Hi,

Have hit upon a problem with an SQL view, and wondered if anyone else had come upon a similar problem.

I have 2 tables in my database:

Categories

Requests

The Categories table contains three fields: CategoryID, ParentID, CategoryName

My Categories table has 3 entries:

CategoryID: 1
ParentID: Null
Title: Category 1

CategoryID: 2
ParentID: 1
Title: Category 2

CategoryID: 3
ParentID: 2
Title: Category 3

The Reqests table contains the following: RequestID, CategoryID, Title etc

My Requests table also has 3 entries:

RequestID: 1
CategoryID: 1
Title: Request 1

RequestID: 2
CategoryID: 2
Title: Request 2

RequestID: 3
CategoryID: 3
Title: Request 3

The problem I am faced with is that I need to build up a select statement which can return all requests filtered by the category ID, but this also needs to include any requests whose sub categories are a sub of the master category (if that makes sense).

Am not sure how I could go about this, I'm not sure what I would need to do to be able to loop up through all the parent ID's until I reach a null value?

Any help on this would be much appreciated.

Matt

Its good that you provided sample data. Can you also provide expected output for a given categoryID of 1 or 2 so we can understand your requirement better?

|||

Here is a query to get you a listing of all top level parents and one child.

select c.CategoryID ParentCategoryID, c.Title ParentTitle , r.RequestID ParentRequestID, r.Title , cc.CategoryID ChildCategoryID, cc.CategoryTitle ChildTitle , cc.RequestID ChildRequestID, cc.ChildRequestTitlefrom Categories cleftouter join (select cx.CategoryID, cx.ParentID, cx.Title CategoryTitle , rx.RequestID, rx.Title RequestTitlefrom Categories cxleftouter join Requests rxon cx.CategoryID = rx.CategoryIDand cx.ParentIDISNOT NULL )as ccon cc.ParentID = c.CategoryIDleftouter join Requests ron c.CategoryID = r.CategoryIDwhere c.ParentIDISNULL

But I do believe that this only gets you part of the way.

|||

Hi,

This article should provide you with some helpful guidance.

http://mssqltips.com/tip.asp?tip=938

|||

Thanks for all of your help, had a look at the recursive common table expressions in SQL server 2005 and looks like I now have my solution.

Cheers,

Matt

|||

Hi,

Managed to get around my first problem using the recursive common table expressions but have now hit upon another as I want to add paging using the Row_Number and custom sorting.

Here is what I have in my stored procedure so far:

DECLARE @.sqlString nvarchar(4000)

IF @.SortExpression = ''
BEGIN
SET @.SortExpression = 'DateCreated DESC'
END

IF @.CategoryID = 0
BEGIN
WITH AllJobRequests AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY @.SortExpression) AS RowNum FROM dbo.JobRequests
)
SELECT * FROM AllJobRequests WHERE RowNum BETWEEN @.Start AND @.End ORDER BY RowNum ASC
END
ELSE
BEGIN
SET @.sqlString = 'WITH GetSubCategories (JobRequestID, CategoryID, ParentID, CategoryName, Depth, RowNum) AS
(
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, 0 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
WHERE dbo.Categories.CategoryID = ' + CONVERT(nvarchar(10), @.CategoryID) + '
UNION ALL
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, GetSubCategories.Depth + 1 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
JOIN GetSubCategories ON Categories.ParentID = GetSubCategories.CategoryID
)
SELECT DISTINCT * FROM GetSubCategories WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @.Start) + ' AND ' + CONVERT(nvarchar(10), @.End) + ' ORDER BY RowNum ASC'
EXEC sp_executesql @.sqlString

This works to an extent apart from the sorting, have a look at the following output gained (the last bold characters are the RowNum order)

2 13 12 Central Heating 1 1
7 12 NULL Plumbing 0 1
6 12 NULL Plumbing 0 2
5 12 NULL Plumbing 0 3
4 12 NULL Plumbing 0 4
3 12 NULL Plumbing 0 5

The results are good in that they return both the output for the master category "Plumbing" and then the child category "Central Heating", but what would I now need to do to change this stored procedure to ensure I order the columns correctly?

Thanks,

Matt