sql select - 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 2008 Forums

Transact-SQL (2008)

sql select

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-02-06 : 08:49:22

Hello,
There is a table as follows:

create table #tblMain(ID int, MyID int, FromID int, ToID int)

insert into #tblMain(ID, MyID, FromID, ToID) values(7, 5, 99, 353)
insert into #tblMain(ID, MyID, FromID, ToID) values(6, 5, 399, 398)
insert into #tblMain(ID, MyID, FromID, ToID) values(5, 5, 346, 543)
insert into #tblMain(ID, MyID, FromID, ToID) values(4, 5, 353, 400)
insert into #tblMain(ID, MyID, FromID, ToID) values(3, 5, 393, 403)
insert into #tblMain(ID, MyID, FromID, ToID) values(2, 5, 307, 306)
insert into #tblMain(ID, MyID, FromID, ToID) values(1, 5, 400, 1238)

select * from #tblMain
drop table #tblMain

For example, you would like to find out where the ToID valueof 1238 originated from?
The answer is 99

or the originator of ToID value of 306 is 307

How is this done in sql please?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-06 : 16:57:50

[code]DECLARE @ToID INT = 1238;

WITH cteSource(Iteration, ID)
AS (
SELECT 0 AS Iteration,
FromID AS ID
FROM #TblMain
WHERE ToID = @ToID

UNION ALL

SELECT s.Iteration + 1 AS Iteration,
t.FromID AS ID
FROM cteSource AS s
INNER JOIN #TblMain AS t ON t.ToID = s.ID
)
SELECT TOP(1) ID,
Iteration AS Hops
FROM cteSource
ORDER BY Iteration DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-02-07 : 03:22:25

Thank you

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-02-07 : 12:27:13

quote:

Originally posted by SwePeso

DECLARE	@ToID INT = 1238;

WITH cteSource(Iteration, ID)
AS (
	SELECT	0 AS Iteration,
		FromID AS ID
	FROM	#TblMain
	WHERE	ToID = @ToID

	UNION ALL

	SELECT		s.Iteration + 1 AS Iteration,
			t.FromID AS ID
	FROM		cteSource AS s
	INNER JOIN	#TblMain AS t ON t.ToID = s.ID
)
SELECT TOP(1)	ID,
		Iteration AS Hops
FROM		cteSource
ORDER BY	Iteration DESC;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Hi, do you know how I can get the data as a table using a function please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-08 : 11:25:41

ITVF

CREATE FUNCTION dbo.fnMyHierarchy
(
    @ToID INT
)
RETURNS TABLE
AS
RETURN  WITH cteSource(Iteration, ID)
        AS (
        	SELECT	0 AS Iteration,
        		FromID AS ID
        	FROM	#TblMain
        	WHERE	ToID = @ToID
        
        	UNION ALL
        
        	SELECT		s.Iteration + 1 AS Iteration,
        			t.FromID AS ID
        	FROM		cteSource AS s
        	INNER JOIN	#TblMain AS t ON t.ToID = s.ID
        )
        SELECT TOP(1)	ID,
        		Iteration AS Hops
        FROM		cteSource
        ORDER BY	Iteration DESC;

Scalar

CREATE FUNCTION dbo.fnMyHierarchy
(
    @ToID INT
)
RETURNS INT
AS
RETURN  WITH cteSource(Iteration, ID)
        AS (
        	SELECT	0 AS Iteration,
        		FromID AS ID
        	FROM	#TblMain
        	WHERE	ToID = @ToID
        
        	UNION ALL
        
        	SELECT		s.Iteration + 1 AS Iteration,
        			t.FromID AS ID
        	FROM		cteSource AS s
        	INNER JOIN	#TblMain AS t ON t.ToID = s.ID
        )
        SELECT TOP(1)	ID,
        		Iteration AS Hops
        FROM		cteSource
        ORDER BY	Iteration DESC;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources