September 16, 2009 at 2:50 pm
This has got me completely stumped.
I have a treeview table that holds data
Item1, ID, ParentID
Each Tree Node points to Data, but some tree nodes have no data.
I need to select the treeview but omit any branches that have no data, HOWEVER I need to keep any branches that have no data if there is data in a lower sub node.
Parent1
Node1
Data
Node2
Data
Node3
Node4
Node5
Data
So we would get all but node 4 returned as it points to no data. Node 3 is returned as node 5 has Data
Any Ideas?
September 16, 2009 at 4:08 pm
Sorry I did post with spaces but they got removed
Parent1
.....Node1
......Data
.....Node2
......Data
.....Node3
.......Node4
.......Node5
.........Data
September 17, 2009 at 5:09 am
I have made some progress, but its a dirty.
I have created a function that looks up all the child nodes to the current node, then checks for data in those nodes and returns true if any found.
This involves a trip to the function for every node in the tree - there must be a more elegant solution.
September 17, 2009 at 12:54 pm
I think i am getting stuck in a loop
ALTER FUNCTION [dbo].[GetEmptyNodes](@ThisSectionID int) Returns int
AS
BEGIN
DECLARE @TmpTree TABLE (SectionID int ,TreeLevel int)
DECLARE @level int
DECLARE @Result int
SET @level = 0
SET @Result = 0
INSERT INTO @TmpTree ( SectionID , TreeLevel) SELECT nSectionID, @level from Web_Sections WHERE nParentSectionID = @ThisSectionID
-- Loop through the levels till the end of the tree
WHILE @@ROWCOUNT > 0
BEGIN
BEGIN
SET @level = @level + 1
INSERT INTO @TmpTree ( SectionID , TreeLevel )
SELECT s.nSectionID, @level
from Web_Sections s
join TmpTree t
on s.nParentSectionID = s.nSectionID
and t.TreeLevel = @level -1
END
WHILE @Result = 0
SET@Result =
(
Select count(*) from [Product_Data]
where webproductgroupid = (Select top 1 SectionID from @TmpTree order by SectionID)
and webproduct = 1 )
Delete from @TmpTree Where SectionID = (Select top 1 SectionID from @TmpTree order by SectionID)
END
RETURN @Result
END
Does anybody have any idea how I can achieve this in a transaction rather than calling a function?
September 17, 2009 at 1:02 pm
One reason you don't have any replies yet might be that it takes too much effort to create a test environment.
Please read and follow the link in my signature on how to post data. I'm sure there are people out there willing to help but simply not having the time to write the test environment...
September 18, 2009 at 5:08 am
Ten Centuries
Thank you for pointing out the error of my ways. It is rude of me to assume that others have time to spend on other peoples problems. I now include some test data and expected results.
This I think is quite a challenge so it would be interesting to see if anybody can get to the bottom of it.
[Code]
DROP TABLE #Web_Sections
DROP TABLE #Product_Data
--Create Sections Table for Treeview
CREATE TABLE #Web_Sections(
[Section text] [nvarchar](50) NULL,
[nSectionID] [int] NOT NULL,
[nParentSectionID] [int] NULL )
--add some sample data
INSERT INTO #Web_Sections
([Section Text],NSectionID,nParentSectionID)
SELECT'Projectors','221','2150' UNION ALL
SELECT'Multimedia','2150','0' UNION ALL
SELECT'Satellite Navigation','2422','0' UNION ALL
SELECT'3M Projectors','2601','221' UNION ALL
SELECT'Viamichelin','2604','2422' UNION ALL
SELECT'Viamichelin Accessories','2605','2604' UNION ALL
SELECT'Garmin Navigation','3544','2422' UNION ALL
SELECT'Garmin Navigation Accessories','3545','3544' UNION ALL
SELECT'Thales Magellan Sat Nav','3688','2422' UNION ALL
SELECT'Thales Magellan Accessories','3689','3688'
--test
--select * from #Web_sections
--create product data table
CREATE TABLE #Product_Data(
[QuickID] [int] NOT NULL,
[Description] [nvarchar](255) NULL,
[WebProductGroupID] [int] NULL,
[WebProduct] [bit] NULL)
--insert some sample data
INSERT INTO #Product_Data
(QuickID,Description,WebProductGroupID,WebProduct)
SELECT'218081','P610 Professional Navigation + 512MB SD Card UK and Ireland','2422','1' UNION ALL
SELECT'378359','Edge 305CAD GPS receiver cycle','3544','1' UNION ALL
SELECT'60547','Language Guide ','3545','1' UNION ALL
SELECT'192344','Case for X960 GPS - black','2605','1' UNION ALL
SELECT'124108','WinTV-NOVA-S USB2 External','2150','1' UNION ALL
SELECT'29373','X22P DLP Projector 2200 ANSI lumens XGA (1024 x 768) 4:3','221','1' UNION ALL
SELECT'108357','1620 Budget Overhead Projector 2100 ANSI 13.6kg','2601','1'
--test
--SELECt * from #Product_Data;
;
--show data structure
WITH MyResult ([Section Text],nSectionID, nParentSectionID, TreeLevel)
AS
(
-- Anchor - Root Nodes
SELECT [Section Text] ,nSectionID, nParentSectionID,
0 AS TreeLevel
FROM #Web_Sections Where nParentsectionID = 0
UNION ALL
-- Recursive
SELECT w.[Section Text] ,w.nSectionID, w.nParentSectionID,
TreeLevel + 1
FROM #Web_Sections AS w
INNER JOIN MyResult r
ON r.nSectionID = w.nParentSectionID
)
-- CTE Statement
SELECT [Section Text],nSectionID, nParentSectionID, TreeLevel , (Select count(*) from #Product_Data where webproductgroupid = nsectionID and webproduct = 1) AS ProductCount
FROM MyResult
order by treelevel
[/code]
I am expecting only sections 3688 and 3689 to be omited formt he results. Section 2604 whilst having no product data needs to be in place to represent the child node 2605 which does have data.
September 18, 2009 at 3:27 pm
Using your sample data from above and the CTE you already provided I added another recursive CTE to get the total amount of products pe section id.
Then I eliminated all rows with productcount = 0.
Since the bottom-up recursive CTE will result in multiple rows per higher level I had to group the values.
You're right, it's definitely a challenge (at least for me)... This is one solution I can think of. Maybe somebody else will have an easier one...
One side note though:
It is rude of me to assume that others have time to spend on other peoples problems.
This comment simply is inappropriate.
If you'd compare the information you provided before the last post (having sample data and all that stuff), wouldn't you say that your last post does include a lot more information than your previous ones?
Just make a simple test:
1) start with an empty query window in SSMS.
2a) take the information from your first post and transform the information into a table that represent the data a similar way your last post does.
2b) copy your sample code in a new window and run it
Compare the time for step 2a and 2b.
See the point?
Even though you have this information already available, we'd have to invest the same time as you did for step 2a to be able to even get started.
;WITH MyResult ([Section Text],nSectionID, nParentSectionID, TreeLevel)
AS
(
-- Anchor - Root Nodes
SELECT [Section Text] ,nSectionID, nParentSectionID,
0 AS TreeLevel
FROM #Web_Sections Where nParentsectionID = 0
UNION ALL
-- Recursive
SELECT w.[Section Text] ,w.nSectionID, w.nParentSectionID,
TreeLevel + 1
FROM #Web_Sections AS w
INNER JOIN MyResult r
ON r.nSectionID = w.nParentSectionID
)
,
cte2 as
(
SELECT [Section Text],nSectionID, nParentSectionID, TreeLevel ,
(Select count(*)
from #Product_Data
where webproductgroupid = nsectionID and webproduct = 1
) AS ProductCount
FROM MyResult
),
sup AS
(
SELECT h.[Section Text], h.nsectionid, h.nparentsectionid,h.TreeLevel,
h.ProductCount,h.ProductCount AS f
FROM cte2 h
WHERE h.treelevel = (SELECT max(treelevel) FROM cte2)
UNION ALL
SELECT h.[Section Text], h.nsectionid, h.nparentsectionid ,h.TreeLevel,
h.ProductCount + sn.ProductCount,h.ProductCount
FROM cte2 h
INNER JOIN sup sn on h.nsectionid = sn.nparentsectionid
)
SELECT [Section Text],nsectionid,nparentsectionid,treelevel,min(f) AS ProductCount
FROM sup
WHERE productcount > 0
GROUP BY [Section Text],nsectionid,nparentsectionid,treelevel
ORDER BY treelevel, nsectionid
DROP TABLE #Web_Sections
DROP TABLE #Product_Data
/* result set
Section TextnsectionidnparentsectionidtreelevelProductCount
Multimedia2150001
Satellite Navigation2422001
Projectors221215011
Viamichelin2604242210
Garmin Navigation3544242211
3M Projectors260122121
Viamichelin Accessories2605260421
Garmin Navigation Accessories3545354421
*/
September 19, 2009 at 2:26 am
Ten Centuries
Thank you so much for spending the time to look and solve this problem for me.
Please do not think my comment was inappropriate. I was actually pointing out that you were right, and that I should have given more information in the first post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply