get all the child categories in the parent category

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 426,807 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,864 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1350 | Replies: 6 | Solved

•

Join Date: Jan 2008

Location: Largo Florida

Posts: 275

Reputation: serkansendur is an unknown quantity at this point

Rep Power: 1

Solved Threads: 23

serkansendur serkansendur is offline

Offline

Posting Whiz in Training

get all the child categories in the parent category

Feb 8th, 2008

what is the required t-sql for getting all the child categories in a parent category?
my table is like this : id | name | parentid

Serkan Şendur
MCAD.NET

•

Join Date: Aug 2006

Location: Egypt

Posts: 762

Reputation: RamyMahrous is on a distinguished road

Rep Power: 4

Solved Threads: 59

RamyMahrous RamyMahrous is offline

Offline

Master Poster

Re: get all the child categories in the parent category

Feb 8th, 2008

Help with Code Tags

(Toggle Plain Text)

SELECT     dbo.Category.Name, dbo.Category.ID
FROM         dbo.Category LEFT OUTER JOIN
                      dbo.Category AS Category_1 ON dbo.Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID IS NULL)

B.Sc Computer Science, Helwan University
Microsoft Student Partner
Personal blog http://ramymahrous.blogspot.com/
Arabic technical blog http://fci-h-ar.blogspot.com/
English technical blog http://fci-h.blogspot.com/

•

Join Date: Nov 2007

Location: � Jogja �

Posts: 2,585

Reputation: Jx_Man is a glorious beacon of light

Rep Power: 11

Solved Threads: 235

Jx_Man

Offline

Posting Maven

Re: get all the child categories in the parent category

Feb 8th, 2008

absolutely RamyMahrous.

Last edited by Jx_Man : Feb 8th, 2008 at 10:28 pm.

Never tried = Never Know
So, Please do something before post your thread.
* PM Asking will be ignored *

•

Join Date: Jan 2008

Location: Largo Florida

Posts: 275

Reputation: serkansendur is an unknown quantity at this point

Rep Power: 1

Solved Threads: 23

serkansendur serkansendur is offline

Offline

Posting Whiz in Training

Re: get all the child categories in the parent category

Feb 11th, 2008

•

Originally Posted by RamyMahrous

Help with Code Tags

(Toggle Plain Text)
SELECT     dbo.Category.Name, dbo.Category.ID
FROM         dbo.Category LEFT OUTER JOIN
                      dbo.Category AS Category_1 ON dbo.Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID IS NULL)
SELECT dbo.Category.Name, dbo.Category.ID FROM dbo.Category LEFT OUTER JOIN dbo.Category AS Category_1 ON dbo.Category.ID = Category_1.ParentID WHERE (Category_1.ParentID IS NULL)

Thanks but this is not what i want. i want to pass cat.id as parameter and get all the childrows in it.
example

catid | catname | parentid
1 | 1 | null
2 |1.1 |1
3 |1.2 |1
4 |1.1.1 |2
5 |1.2.1 |3

when i pass a catid = 1 as parameter to procedure i want to get all these records except the first main root one.

Last edited by serkansendur : Feb 11th, 2008 at 3:05 am.

Serkan Şendur
MCAD.NET

•

Join Date: Aug 2006

Location: Egypt

Posts: 762

Reputation: RamyMahrous is on a distinguished road

Rep Power: 4

Solved Threads: 59

RamyMahrous RamyMahrous is offline

Offline

Master Poster

Re: get all the child categories in the parent category

Feb 12th, 2008

Sorry for being late as I was in Army last two days..

Help with Code Tags

(Toggle Plain Text)

SELECT     Category_1.Name
FROM         Category LEFT OUTER JOIN
                      Category AS Category_1 ON Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID = 1)

•

Join Date: Jan 2008

Location: Largo Florida

Posts: 275

Reputation: serkansendur is an unknown quantity at this point

Rep Power: 1

Solved Threads: 23

serkansendur serkansendur is offline

Offline

Posting Whiz in Training

Re: get all the child categories in the parent category

Feb 13th, 2008

•

Originally Posted by RamyMahrous

Sorry for being late as I was in Army last two days..
Help with Code Tags

(Toggle Plain Text)
SELECT     Category_1.Name
FROM         Category LEFT OUTER JOIN
                      Category AS Category_1 ON Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID = 1)
SELECT Category_1.Name FROM Category LEFT OUTER JOIN Category AS Category_1 ON Category.ID = Category_1.ParentID WHERE (Category_1.ParentID = 1)

Thanks but this is not what i wanted, i want to see the childs recursively, i want to see the grand childs, childs of grands childs and so on.

Serkan Şendur
MCAD.NET

•

Join Date: Jan 2008

Location: Largo Florida

Posts: 275

Reputation: serkansendur is an unknown quantity at this point

Rep Power: 1

Solved Threads: 23

serkansendur serkansendur is offline

Offline

Posting Whiz in Training

Re: get all the child categories in the parent category

Feb 13th, 2008

the t-sql below worked :

Help with Code Tags

(Toggle Plain Text)

set statistics time on
set statistics io on
declare @Topid_in int -- The top level we want to resolve children for
select @topid_in = 1;
with Hierarchycte (id, ParentID, name) as	
(select id, parentid, name
	from cat
	where id = @topid_in
	union all	
select cat.id, cat.parentid	,cat.name
from cat	
inner join hierarchycte	
	on cat.parentid = hierarchycte.id)
select * from hierarchycte

Serkan Şendur
MCAD.NET