September 23, 2013 at 3:21 pm
I have two separate tables in my database ReportCategories and Reports. I am trying to determine how I can pull a listing of all the report categories that are ancestors of the reports which can be displayed by the user. (Some users will be able to see all reports, and some users will only be able to see a sub-set of the available reports. I want to build the report menu on a per-user basis in my program. If the user cannot see any of the reports in a particular category, I do not want that category to be displayed.)
If this cannot be done from T-SQL, I can implement this in the program logic, but it seems like this should be possible to query from the database. Any help would be appreciated!
Sample Table Structure:
CREATE TABLE [dbo].[tblTestCategories](
[ID] [int] NOT NULL,
[CategoryName] [varchar](50) NOT NULL,
[ReportCategoryID] [int] NULL,
CONSTRAINT [PK_tblTestCategories] PRIMARY KEY CLUSTERED ([ID] ASC))
GO
ALTER TABLE [dbo].[tblTestCategories] ADD CONSTRAINT [DF_Table_1_ReportName] DEFAULT ('') FOR [CategoryName]
GO
ALTER TABLE [dbo].[tblTestCategories] WITH CHECK ADD CONSTRAINT [FK_tblTestCategories_tblTestCategories] FOREIGN KEY([ReportCategoryID])
REFERENCES [dbo].[tblTestCategories] ([ID])
GO
ALTER TABLE [dbo].[tblTestCategories] CHECK CONSTRAINT [FK_tblTestCategories_tblTestCategories]
GO
CREATE TABLE [dbo].[tblTestReports](
[ID] [int] NOT NULL,
[ReportName] [varchar](50) NOT NULL,
[CategoryID] [int] NULL)
GO
ALTER TABLE [dbo].[tblTestReports] ADD CONSTRAINT [DF_tblTestReports_ReportName] DEFAULT ('') FOR [ReportName]
GO
ALTER TABLE [dbo].[tblTestReports] WITH CHECK ADD CONSTRAINT [FK_tblTestReports_tblTestCategories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[tblTestCategories] ([ID])
GO
ALTER TABLE [dbo].[tblTestReports] CHECK CONSTRAINT [FK_tblTestReports_tblTestCategories]
GO
Sample Data:
INSERT INTO [dbo].[tblTestCategories] ([ID], [CategoryName], [ReportCategoryID])
SELECT 1, 'Test Category 1', NULL
UNION ALL
SELECT 3, 'Test Sub Category 1', 1
UNION ALL
SELECT 2, 'Test Category 2', NULL
UNION ALL
SELECT 5, 'Test Sub Category 2', 2
UNION ALL
SELECT 7, 'Test Category 3', NULL
UNION ALL
SELECT 6, 'Test Sub Category 3', 7
UNION ALL
SELECT 8, 'Test Sub Sub Category 3', 6
UNION ALL
SELECT 4, 'Test Sub Sub Sub Category 3', 8
UNION ALL
SELECT 10, 'Test Category 4', NULL
UNION ALL
SELECT 11, 'Test Category 5', NULL
UNION ALL
SELECT 9, 'Test Sub Category 5', 11
GO
INSERT INTO [dbo].[tblTestReports] ([ID], [ReportName], [CategoryID])
SELECT 1, 'Report 1', 1
UNION ALL
SELECT 2, 'Report 2', NULL
UNION ALL
SELECT 3, 'Report 3', 2
UNION ALL
SELECT 4, 'Report 4', 3
UNION ALL
SELECT 5, 'Report 5', 4
UNION ALL
SELECT 6, 'Report 6', 5
UNION ALL
SELECT 7, 'Report 7', 7
UNION ALL
SELECT 8, 'Report 8', 7
UNION ALL
SELECT 9, 'Report 9', 1
UNION ALL
SELECT 10, 'Report 10', NULL
UNION ALL
SELECT 11, 'Report 11', NULL
UNION ALL
SELECT 12, 'Report 12', 8
UNION ALL
SELECT 13, 'Report 13', NULL
UNION ALL
SELECT 14, 'Report 14', NULL
UNION ALL
SELECT 15, 'Report 15', 9
UNION ALL
SELECT 16, 'Report 16', NULL
UNION ALL
SELECT 17, 'Report 17', NULL
UNION ALL
SELECT 18, 'Report 18', NULL
UNION ALL
SELECT 19, 'Report 19', NULL
UNION ALL
SELECT 20, 'Report 20', NULL
GO
I have been able to use the following query (using a CTE) to order all the categories together, but I have not been able to determine a way to join the reports table to this to limit the return to only categories with a report descendant:
WITH ReportCategoryList AS (
SELECT *, 1 AS CategoryLevel
FROM tblTestCategories
WHERE ReportCategoryID IS NULL
UNION ALL
SELECT TC.*, RCL.CategoryLevel + 1
FROM tblTestCategories AS TC INNER JOIN ReportCategoryList RCL ON TC.ReportCategoryID = RCL.ID
WHERE TC.ReportCategoryID IS NOT NULL)
SELECT *
FROM ReportCategoryList RCL
ORDER BY RCL.CategoryLevel, RCL.ID
What should happen is that I should be able to pull a listing of the report categories, NOT including
Test Category 4
, as that is the only report category (or sub-category), that does not have at least 1 report as a descendant.
If you need more clarification, let me know and I will do my best to try to clarify what I am looking for:crazy:
September 24, 2013 at 8:34 am
WITH ReportCategoryList AS (
SELECT *, 1 AS CategoryLevel
FROM tblTestCategories
WHERE ReportCategoryID IS NULL
AND ID IN (SELECT ReportCategoryID from tblTestCategories)
UNION ALL
SELECT TC.*, RCL.CategoryLevel + 1
FROM tblTestCategories AS TC INNER JOIN ReportCategoryList RCL ON TC.ReportCategoryID = RCL.ID
)
SELECT *
FROM ReportCategoryList RCL
ORDER BY RCL.CategoryLevel, RCL.ID
September 24, 2013 at 1:40 pm
sribe, while your query does remove the category for this particular set of test data, my question is a bit more general in nature. This test data is just one possible way of setting up the categories/sub categories, and my program will actually be able to allow the end users to setup their own categories/sub categories for the reporting. Your query will only remove the category if no subcategories reference it, but what I am wanting to do is to remove all categories (and their subcategories), if there are no reports (which are kept in a different table), which link to either the main category or any of its subcategories.
For example, if I had a report that was linked to Test Sub Sub Sub Category 3, then Test Category 3, Test Sub Category 3, Test Sub Sub Category 3, and Test Sub Sub Sub Category 3 should all be visible (and in the test data, Report 5 is linked to this category.) But, if Report 5 was linked to, for example, Test Category 3, then none of the subcategories for Test Category 3 should be displayed, only the main test Category 3. Or if that report was linked to Test Category 4, Test Category 3 and all subcategories would be hidden, and Test Category 4 would be displayed.
Hope that clears up what I am looking for. Again, this may not be possible with a query from the database, and if not that's fine. I am just trying to make sure that I am not missing something.
September 24, 2013 at 4:42 pm
You can flip the anchor and recursive portion. The anchor becomes any folder that contains a report. In the below example the CategoryLevel is inverted (level that contains a report is 1).
WITH ReportCategoryList AS (
SELECT C.*, 1 AS CategoryLevel
FROM tblTestCategories C
INNER JOIN tblTestReports R
ON C.ID = R.CategoryID
UNION ALL
SELECT TC.*, RCL.CategoryLevel + 1
FROM tblTestCategories AS TC INNER JOIN ReportCategoryList RCL ON TC.ID = RCL.ReportCategoryID
--WHERE TC.ReportCategoryID IS NOT NULL
)
SELECT ID, CategoryName, ReportCategoryID, MAX(CategoryLevel) as CategoryLevel
FROM ReportCategoryList RCL
GROUP BY ID, CategoryName, ReportCategoryID
ORDER BY ReportCategoryID, RCL.ID, MAX(CategoryLevel) DESC
September 25, 2013 at 7:53 am
Thank you very much:-D
That was the piece that I was missing!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply