quote:

---

Originally posted by vaibhavktiwari83

Whats the logic behind ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

---

DECLARE @Table1 TABLE (
	[No] INT PRIMARY KEY
	, [Details] VARCHAR(50)
	)

INSERT @Table1 ([No], [Details])
      SELECT 1, 'the'
UNION SELECT 2, 'quick'
UNION SELECT 3, 'brown'
UNION SELECT 4, 'fox'
UNION SELECT 5, 'jumps'
UNION SELECT 6, 'over'
UNION SELECT 7, 'the'
UNION SELECT 8, 'lazy'


DECLARE @Table2 TABLE (
	[No] INT
	, [Parent] INT
	
	PRIMARY KEY ([No], [Parent])
	)

INSERT @Table2 ([No], [Parent])
      SELECT 2, 1
UNION SELECT 3, 1
UNION SELECT 4, 3
UNION SELECT 5, 4

DECLARE @param INT SET @param = 1

; WITH
	CTE ([No], [Details])
	AS (
		-- Anchor
		SELECT
			[No]
			, [Details]
		FROM
			@Table1
		WHERE
			[No] = @param

		-- Recursive
		UNION ALL SELECT
			t2.[No]
			, t1.[Details]
		FROM
			@table2 AS t2
			JOIN @table1 AS t1 ON t1.[No] = t2.[No]
			JOIN CTE AS c ON c.[No] = t2.[parent]
	)

SELECT
	[No]
	, [Details]
FROM
	CTE
ORDER BY
	[No]

quote:

---

; WITH
CTE ([No], [Details])
AS (
-- Anchor
SELECT
[No]
, [Details]
FROM
@Table1
WHERE
[No] = @param

-- Recursive
UNION ALL SELECT
t2.[No]
, t1.[Details]
FROM
@table2 AS t2
JOIN @table1 AS t1 ON t1.[No] = t2.[No]
JOIN CTE AS c ON c.[No] = t2.[parent]
)

---

quote:

---

Originally posted by Transact Charlie

Hi boOtl3ss.

A Common Table Expression (CTE for short) is like a derived table that you can declare before ONE statement. As you can see here they are useful for recursion (set based recursion).

What this code does is analogous to this:

1) It selects the details for the first line (where No = 1)

2) It then JOINS the result from 1 and works out the children of that.

3) Repeat -- Works out the JOINS for (2)....

And so on until no more results are found.

Then finally it delivers the results.

Here's more info:
http://msdn.microsoft.com/en-us/library/ms190766.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie

indeed .. more power!



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

DECLARE @Table1 TABLE (
	[No] INT PRIMARY KEY
	, [Details] VARCHAR(50)
	)

INSERT @Table1 ([No], [Details])
      SELECT 1, 'the'
UNION SELECT 2, 'quick'
UNION SELECT 3, 'brown'
UNION SELECT 4, 'fox'
UNION SELECT 5, 'jumps'
UNION SELECT 6, 'over'
UNION SELECT 7, 'the'
UNION SELECT 8, 'lazy'
UNION SELECT 9, 'dog'

UNION SELECT 10, 'white'
UNION SELECT 11, 'men'
UNION SELECT 12, 'can''t'
UNION SELECT 13, 'jumps'

DECLARE @Table2 TABLE (
	[No] INT
	, [Parent] INT
	
	PRIMARY KEY ([No], [Parent])
	)

INSERT @Table2 ([No], [Parent])
-- Quick brown fox jumps
      SELECT 2, 1
UNION SELECT 3, 2
UNION SELECT 4, 3
UNION SELECT 5, 4

-- White Men Can't Jumps (extra s...)
UNION SELECT 11, 10
UNION SELECT 12, 11
UNION SELECT 13, 12

DECLARE @param VARCHAR(255) SET @param = 'jumps'

;WITH
	findRoot (
		[path]
		, [No] 
		, [Parent]
		, [level]
		)
	AS (
		-- Anchor
		SELECT
			CAST(t1.[Details] AS VARCHAR(MAX))
			, t2.[No]
			, t2.[Parent]
			, 0
		FROM
			@table1 AS t1
			JOIN @table2 AS t2 ON t2.[No] = t1.[No]
		WHERE
			t1.[Details] = @param

		-- Recursive
		UNION ALL SELECT
			CAST(fr.[path] + ' -> ' + t1.[details] AS VARCHAR(MAX))
			, fr.[No]
			, t2.[parent]
			, fr.[level] + 1
		FROM
			findRoot AS fr
			JOIN @table1 AS t1 ON t1.[No] = fr.[Parent]
			JOIN @table2 AS t2 ON t2.[No] = t1.[No]
	)
-- Results
SELECT
	leaf.[Details] AS [Leaf]
	, leaf.[No] AS [Leaf No]
	, finalRoot.[Details] AS [Root]
	, finalRoot.[No] AS [Root No]
	, rootPath.[Path] + ' -> ' + finalRoot.[Details] AS [Root Path]
FROM
	@table1 AS Leaf

	JOIN (
		SELECT
			fr.[No] AS [No]
			, fr.[parent] AS [Parent]
			, fr.[path] AS [Path]
			, ROW_NUMBER() OVER(
				PARTITION BY fr.[No]
				ORDER BY fr.[level] DESC
				)
				AS [rowPos]
		FROM
			findRoot AS fr
		)
		AS rootPath ON
			rootPath.[No] = Leaf.[No]
			AND rootPath.[rowPos] = 1

	JOIN @table1 AS finalRoot ON finalRoot.[No] = rootPath.[Parent]

(7 row(s) affected)
Leaf       Leaf No     Root       Root No     Root Path
---------- ----------- ---------- ----------- --------------------------------------------------
jumps      5           the        1           jumps -> fox -> brown -> quick -> the
jumps      13          white      10          jumps -> can't -> men -> white

quote:

---

Originally posted by Transact Charlie

Results:

(7 row(s) affected)
Leaf       Leaf No     Root       Root No     Root Path
---------- ----------- ---------- ----------- --------------------------------------------------
jumps      5           the        1           jumps -> fox -> brown -> quick -> the
jumps      13          white      10          jumps -> can't -> men -> white

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---