Recursive query - 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)

Recursive query

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2010-06-24 : 10:35:02

Hello All,
I need to write a query which would give me the following in hierarchy manner.

Item A is used to produce Item B and again Item B is used to produce Item C.
So Item A would have 2 parents Item B and Item C

Item A - Item B
Item B - Item C

How would I write recursion query to get to all the parents for a single Item?

Thanks,
-B

Llewellyn
Starting Member

13 Posts

Posted - 2010-06-25 : 08:02:19

-- test data
declare @TableA table (Child char(1),Parent char(2))
Insert into @TableA
select 'A','B'
UNION
SELECT 'B','C'
UNION
SELECT 'D','E'

Declare @Child char(1)
DECLARE @TableB TAble (Result char(1))

--
SELECT @Child = 'D'

Insert INto @TableB
SELECT Parent
FROM @TableA
WHERE Child = @Child

WHILE @@ROWCOUNT <> 0
Insert INto @TableB
SELECT Parent
FROM @TableA
WHERE Child in (SELECT Result FROM @TableB)
AND Parent not in (SELECT Result FROM @TableB)

SELECT * FROM @TableB

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-25 : 08:46:09

you are probably better using a recursive CTE (common table expression).

Have a look in the docs for that and then if you can't work it out post more information including your table structure, sample data and required output.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources