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.
| Author |
Topic |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2008-03-13 : 09:20:22
|
| Hii have a table named categorymastercategoryid catname parentid1 Boxing 02 Tennis 03 Basketball 04 MayWeather 15 Tyson 16 Clinton woods 17 RogerFederer 28 Micheal 39 Hingis 2so if i give input say categoryid=1[This falls under main category-boxing]i need to get result as1 boxing [main category]4 mayweather [sub category]5 tyson [sub category] 6 clinton woods [sub category]if i give categoryid=5[Note:Tyson]result should be as 1 boxing [main category]5 tyson [sub category]hope u can get my questionThanks in advance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-13 : 09:26:41
|
Read Books Online (the SQL Server help file) about recursive CTE's. There are examples of how to do this.There are also many examples here at SQLTeam how to do this.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-13 : 22:46:37
|
| Peso is right in that you could use a CTE for this if it goes back more than 1 level. The more fundemental problem appears to be that your table is serving two functions, category and competetors (for want of a better term).Without knowing the gory details, my instinct is that you should split them into two tables.That said, if you only have one level you can fudge it by getting the me/parents/children.select * from categorymaster where categoryid=@cat or parentid=@catunion all select c2.* from categorymaster c1 inner join categorymaster c2 on c1.parentid=c2.categoryidwhere c1.categoryid=@catorder by parentid, categoryid |
|
|
|
|
|
|
Parent and Child Records From same table