Help with a recursive Query!!!

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)

Help with a recursive Query!!!

Author

Topic

dbayona
Starting Member

4 Posts

Posted - 2007-12-20 : 00:10:17

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

Thanx
Diego Bayona.

pd:
for example:
the root of TaxID = 259 (P_TAXONOMY) is 1 (TAXONOMY)

matty
Posting Yak Master

161 Posts

Posted - 2007-12-20 : 00:35:03

;
with cte(TaxIDParent,tax_name,taxlevel)
as
(
select TaxIDParent,tax_name,taxlevel from TAXONOMY where TaxID = @TaxID
union all
select t.TaxIDParent,t.tax_name,t.taxlevel from TAXONOMY t join cte c on c.TaxIDParent = t.TaxID
)

select tax_name from cte where taxlevel =0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 00:59:08

Try this:-

With Taxonomy_CTE (TaxID,TaxParentID,TaxName) AS
(
SELECT TaxID,TaxParentID,tax_name
FROM TAXONOMY t
INNER JOIN P_TAXONOMY p
ON p.TaxID=t.TaxID
WHERE p.TaxID=259

UNION ALL

SELECT t.TaxID,t.TaxParentID,t.tax_name
FROM TAXONOMY t
INNER JOIN Taxonomy_CTE c
ON t.TaxID=c.TaxIDParent

)

SELECT TaxID,TaxParentID,TaxName FROM Taxonomy_CTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources