find topnode name - 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)

find topnode name

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-28 : 01:23:25

Hi

I have a table (tbl_Products) that look like this..

NodeID = Int PK
ParentNodeID = Int
Text = nVarChar
CustID = Int

And have data like this..


1  NULL  TopNodeName 1    23
2  1     SubFolder 1      23
3  2     Product 1        23
4  NULL  TopNodeName 2    23
5  4     Product 2        23
6  NULL  TopNodeName 1    33
7  6     SubFolder 1      33
8  7     Product 1        33

Then I have a query like this..


SELECT     dbo.tbl_Products.Text, dbo.tbl_OrderInfo.CartID, dbo.tbl_Order.NodeID, dbo.tbl_Order.Quantity
FROM         dbo.tbl_Order INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_OrderInfo ON dbo.tbl_Order.CartID = dbo.tbl_OrderInfo.CartID
WHERE     (dbo.tbl_OrderInfo.CartID = N'111-22-779')

I wonder if it possible to find out what the NodeID's TopNodeName is? and then add that to the query. So that I would find out that "product 1" topnode is "TopNodeName 1". Can that be achieved?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 01:32:27

Yes, you can use a recursive CTE.

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-28 : 02:53:25

Thanks, I will google and give it a try. But it seem a little tricky....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 03:47:21

There are examples in Books Online too.

The only tricky is to know the anchor part and the recursive part.

E 12°55'05.63"
N 56°04'39.26"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-28 : 04:22:28

this what u want?

DECLARE @T TABLE
(
		NodeID  Int,
		ParentNodeID  Int,
		[Text]  nVarChar(100),
		CustID  Int
)

INSERT INTO @T
SELECT 1,  NULL,  'TopNodeName 1',    23 UNION ALL
SELECT 2,  1,     'SubFolder 1',      23 UNION ALL
SELECT 3,  2,     'Product 1',        23 UNION ALL
SELECT 4,  NULL,  'TopNodeName 2',    23 UNION ALL
SELECT 5,  4,     'Product 2',        23 UNION ALL
SELECT 6,  NULL,  'TopNodeName 1',    33 UNION ALL
SELECT 7,  6,     'SubFolder 1',      33 UNION ALL
SELECT 8,  7,     'Product 1',        33


; WITH Cte (NodeID, ParentNodeID, [Text], CustID, RootNode)
AS
(
		SELECT NodeID, ParentNodeID, [Text], CustID, NodeID
		FROM @T
		WHERE ParentNodeID IS NULL
		
		UNION ALL
		
		SELECT T.NodeID, T.ParentNodeID, T.[Text], T.CustID, C.RootNode
		FROM Cte C
		INNER JOIN @T T ON T.ParentNodeID = C.NodeID
)

SELECT C.NodeID, C.ParentNodeID, C.[Text], C.CustID, T.[Text] AS 'ParentNode'
FROM Cte C
INNER JOIN @T T ON T.NodeID = C.RootNode
ORDER BY 1

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-28 : 04:38:39

Hi PeterNeo

Yes, this give me the topnode name as I wanted. But how do I implement that logic in my exsisting query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:03:19

Create a view like this

CREATE VIEW dbo.vwGetTopNodeNames
AS

WITH Yak(CustID, NodeID, TopNodeText)
AS (
	SELECT	CustID,
		NodeID,
		[Text]
	FROM	tbl_Products
	WHERE	ParentNodeID IS NULL

	UNION ALL

	SELECT		s.CustID,
			s.NodeID,
			y.TopNodeText
	FROM		Yak AS y
	INNER JOIN	tbl_Products AS s ON s.ParentNodeID = y.NodeID
)

SELECT	CustID,
	NodeID,
	TopNodeText
FROM	Yak

And then you just join the view from your original query.

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-28 : 06:26:35

Hi Peso

Why do I get a incorrect syntax near ")" when running this ...

CREATE VIEW dbo.vwGetTopNodeNames
AS

WITH Yak(NodeID, TopNodeText)
AS (
SELECT NodeID,[Text]
FROM tbl_Products
WHERE ParentNodeID IS NULL

UNION ALL

SELECT s.NodeID,y.TopNodeText
FROM Yak AS y
INNER JOIN tbl_Products AS s ON s.ParentNodeID = y.NodeID
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:29:13

Yes, you will need the final SELECT too.

CREATE VIEW dbo.vwGetTopNodeNames
AS

WITH Yak(CustID, NodeID, TopNodeText)
AS (
	SELECT	CustID,
		NodeID,
		[Text]
	FROM	tbl_Products
	WHERE	ParentNodeID IS NULL

	UNION ALL

	SELECT		s.CustID,
			s.NodeID,
			y.TopNodeText
	FROM		Yak AS y
	INNER JOIN	tbl_Products AS s ON s.ParentNodeID = y.NodeID
)

SELECT	CustID,
	NodeID,
	TopNodeText
FROM	Yak

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-02-28 : 06:45:57

This is absolutly fantastic, Thank you both for all help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources