Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 20th, 2007, 01:15 AM
Registered User
Join Date: Jul 2007
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with a recursive Query!!!

i have two tables, one called P_TAXONOMY with these columns:

IDObject          TaxID    
   DB1               259

and one hierarchical table called (TAXONOMY) with this columns:

TaxID    TaxIDParent   tax_name         taxlevel
259        256          P.O                3

256        4             A                 2

4           1            B                 1

1           0            C                 0

i get the TaxID from P_TAXONOMY and i have to look for recursively its father (level 0)  in the TAXONOMY table; I'm pretty sure the solution to this involves some type of recursive query but if anyone here can help me out I'd really appreciate it.

i have the next query but only for  3 levels, i need it for n levels (from any leaf)

select stf.tax_name
from TAXONOMY stf,
(select st0.TaxIDParent
from TAXONOMY st0, (select st.TaxIDParent
from P_TAXONOMY spt, TAXONOMY st                                                                  where spt.TaxID = st.TaxID ) as u
where st0.TaxID = u.TaxIDParent ) as cons
where stf.TaxID = cons.TaxIDParent
group by tax_name

Diego Bayona.

pd:for example:
the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)
Old December 20th, 2007, 07:40 AM
Friend of Wrox
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts

-- Prepare sample data
DECLARE    @Taxonomy TABLE (TaxID INT, TaxIDParent INT, Tax_Name VARCHAR(10), TaxLevel INT)

INSERT    @Taxonomy
SELECT    259, 256, 'P.O', 3 UNION ALL
SELECT    256, 4, 'A', 2 UNION ALL
SELECT    4, 1, 'B', 1 UNION ALL
SELECT    1, 0, 'C', 0

-- Prepare recursion
DECLARE    @StartingNode INT

SET    @StartingNode = 259

;WITH Yak (TaxID, ParentID)
AS (
    SELECT    TaxID,
    FROM    @Taxonomy
    WHERE    TaxID = @StartingNode


    SELECT        t.TaxID,
    FROM        @Taxonomy AS t
    INNER JOIN    Yak AS y ON y.ParentID = t.TaxID

SELECT    TaxID AS RootNode
FROM    Yak
WHERE    ParentID = 0

Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP to query a SQL recursive tree chame Classic ASP Basics 4 April 10th, 2012 07:59 PM
Recursive Query for Formatted Output Itech SQL Server 2005 11 June 12th, 2008 04:32 PM
Recursive Coding arnabghosh Classic ASP Professional 2 July 9th, 2007 02:06 AM
recursive query cathiec SQL Language 2 August 25th, 2006 05:58 AM
recursive XSLT Help boates XSLT 2 January 11th, 2006 03:50 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.