How could I get the records recusrively (nested)?

How could I get the records recusrively (nested)?
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***

how can i use COUNT in my stored procedure?

hello,

i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums

How can i use the COUNT function in my stored procedure to take only first n Categories?

I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...

here is my stored procedure (i use it for two nested Repeaters)

ALTER PROCEDURESubCategoriiInCategorii

CategoryIDint)

AS

SELECTCategoryID,Name, DescriptionFROMCategoriesWHERECategoryID = @.CategoryID

ORDER BYName

SELECTp.SubCategoryID, p.Name,p.CategoryIDFROMSubCategorii p

ORDER BYp.Name

i hope you understand what i mean, thank you

Hi,

Have a look at this threadhttp://forums.asp.net/t/1138145.aspx it will tell you how to get N first rows or in otherwords N top rows.

Hope it will help you out.

Thanks and best regards,

|||

if i use the stored procedures from there i get error from the nested repeater...

i tryied this, but i get an error: invalid column name RowNumber ...why the alias don't work?

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategorii

WHEREDepartamentID = @.DepartamentIDANDRowNumber = 3

|||

Hi,

Actually I don't exactly get what are you trying to do in the above stored procedure.

Thanks and best regards,

|||

if i execute the stored procedure without the "AND" part, in the RowNumber i have values from 1 to 9 (counts how many CategoriyID's i have)

i think it work, try to execute the procedure

thank you

i just resolved it, here is the stored procedure

SELECT*FROM(

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategoriiWHEREDepartamentID = @.DepartamentID

)ASMyTable

WHERERowNumber <=5

i think is good for what i need, thank you again for help