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
