Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Parent child list

hi all,

i have a table with column of category related to other category of other rows - sub category (self join).
A
--------------------------
|               |              |
AA            AB            AC
|               |              |
|               |              ACA
|               |
|               |-------------------------
|               |              |               |
|      ABA      ABB      ABC
|                              |               |
|                              |               |------------
|                              |               |               |
|                              ABBA         ABCA         ABCB
|------------
|              |
AAA          AAB

1. Parent category may have none or more sub category, which also may have none or more sub sub category... until n deep (undefine)
2. Sub category must only have one parent category.

Questions:
-----------

1. How I'm going to list all child of parent category/sub category using stored proc. to produce result like below:

List all category below AB, will procude result set like this:-

AB
  |-------ABA
  |
  |-------ABB
  |         |---ABBA
  |
  |-------ABC
             |---ABCA
             |
             |---ABCB

2. How to list all parent category of sub category ABCB

List all parent until root parent of ABCB

-A
-AB
-ABC

if possible i like to have solution in sql statement (t-sql, MSSQL, esp for the second question) or visual basic. please, i hope somebody can help me on this.
0
khairil
Asked:
khairil
  • 7
  • 5
  • 3
  • +1
2 Solutions
 
SjoerdVerweijCommented:
2.

Create Proc Parents
  @CategoryID VarChar(5)
As
Begin

  set nocount on

  declare table @t(categoryid varchar(5))

  select @categoryid = parentcategoryid from category where categoryid = @categoryid

  while (@categoryid is not null)
    begin
      insert into @t values(@categoryid))
      select @categoryid = parentcategoryid from category where categoryid = @categoryid
    end

  select * from @t

  set nocount off
end
go


Working on 1.
0
 
SjoerdVerweijCommented:
How would you like 1. formatted? Just a set of strings?
0
 
biglaxman17Commented:
I know this is not what you are looking for but for Oracle you can use a

CONNECT BY   'Parent column_name' =  PROIR 'column_name'
start with 'Parent Column_name is NULL;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SjoerdVerweijCommented:
Heh. Yes, and in SQL Server 2005 you can use a CTE.

Is it 2005 yet? :-)
0
 
khairilAuthor Commented:
1. for the first one, i more prefer in tabular dataset, which i can maninpulate later using client software.

2. i am using MSSQL 2000. i do have oracle 9i and beta version of yukon (SQL 2005) but i must do this in MSSQL 2000.

0
 
biglaxman17Commented:
I forgot to say on thing on the connect by for Oracle

You can easily format the output by LPAD('Column',LEVEL,'     ').  The level will tell you how many levels you are into your tree.
0
 
khairilAuthor Commented:
emmm.. i still looking a solution in mssql stored proc. i hope somebody out there have the idea on doing these :(...
0
 
solution46Commented:
khairil,

should have an answer for you shortly :)


SjoerdVerweij,

sound familiar??? Agree with you on the hierarchy here :)))



s46
0
 
solution46Commented:
khairil,

Will be able to this as SQL Server sproc, but where do you eventually need to display this? Would a VB treeview be any use - if so I can dig up some sample code from somewhere.

s46.
0
 
khairilAuthor Commented:

yup it just like vb treeview, but this one i like to bound it with my customize user control (make with vb) and also result print out on paper. for real life example is like the one they use in forum, here is an example on codeproject... 'the lounge of codeproject' url (about pet lover :)),  http://www.codeproject.com/lounge.asp#xx921525xx.

if possible, i like to have a dataset return in order i specified on my second question, minimizing data fetch from the server.
0
 
solution46Commented:
Ah, in this case it isn't really a SQL Server sproc you're after.

The easiest way of doing this (may not be the most efficient, but unless your treeview is very complex (many branches, 100,000s of nodes) and it is hit a lot, I wouldn't think you would notice.

First, set up a simple sproc along the following lines...

CREATE PROC spCategoryGetChildren
  @CategoryID int

AS

SELECT CategoryID, CategoryName   -- or whatever fields you need
FROM Category
WHERE ParentCategoryID = @CategoryID

GO


You now need two routines in VB along the following lines (pseudo-code; I'll leave you to write the code out fully). I have assumed VB6 for the ADO stuff here; if you are using .NET you'll need to change it accordingly.

First, at module level, declare a connection (mcnn) and command (mcmd)



Public Sub TreeShowNodes (pintNodeID as integer, pintParentLevel as integer)
    Dim rst as recordset

    dim intNodeID as integer
    dim intLevel as integer

    mcmd.CommandType = Stored Procedure
    mcmd.Text = 'spCategoryGetChildren'

    Set rst = mcmd.Execute

    Do Until rst.EOF
        intNodeID = rst!CategoryID
        strNodeText = rst!CategoryName
       
        intLevel = pintParentLevel + 1


        'generate your tree line here. How you do this depends on the tree you're using,
        'but you have the variables you will need
        'intNodeID is the CategoryID
        'strNodeText is the Category Name
        'intLevel is the number of indentations you need

        'now call this function again, passing the category id and level back
        'this will draw lines for all child nodes (categories) and recurse down each branch until it reaches the end
        TreeShowNodes(intCategoryID, intLevel)

        rst.MoveNext

    Loop

End Sub


Public Sub DrawTree (pintStartingNode as integer)
    'simply call the TreeShowNodes Sub with the starting category and level

    TreeShowNodes(pintStartingNode, 0)

End Sub



That's pretty much it; as you get the hang of it you may want to work out how to remember view state (you can set properties on each node object to say whether it is expanded or not) and so on.

Hope this helps,

s46.
0
 
khairilAuthor Commented:
emmm.. impressive, even though i not going to use treeview for this but it still give me idea, but i cannot do this in stored proc for it limitation.

still i do hope some other solution including adding other fields and customize implementation of insert/update so that i can do select ... order by ...

anyway, thanks to both of you. right now it is the best way i have,  i just add other 40 points, and split to both of you, solution46 and SjoerdVerweij. thanks :)
0
 
solution46Commented:
khairil,

you're welcome. It is possible to do it in a stored procedure (use roughly the same process as outlined above) but you lose a lot of flexibility. For instance, from the example you gave, it looks you're doing this in ASP? If so, then allowing VB to take care of building the tree means you can add your <a href>s in as required, you can build in images and so on. If you build the image of the tree in SQL Server, you will have to pass all sorts of information to SQL Server for it to be able to work out what the links are and where they should go.

As an idea of the performance; the example you gave would require ten hits on the database to populate. If you only populated each branch as it was required (i.e. by the user clicking on the link), it would only need one refresh from the database when you open the page.

Another consideration is how much information you are sending over the internet and how long you want to take to build the page - if you try to return too much you're probably going to take longer just building the tree than SQL Server will retrieving the records.

Regards, and cheers for the points,

s46.
0
 
khairilAuthor Commented:
actually i not targeting for internet, but the application i going to develop behave like the code project forum.

i'm using COM build with visual basic, hence i manage to use it on desktop application and internet application.

still i like to do all the data retreival/sorting at database layer to minimize data transfer between application and reducing recordset opening/closing.
0
 
khairilAuthor Commented:
emmm... i have download code project sample forum, they do have parent id but they implement sorting by on other field.
0
 
khairilAuthor Commented:
0
 
solution46Commented:
From the looks of that, you should just be able to duplicate the functionality of the Access database they provide. Why not just leave it working as per the template?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now