Solved

Parent child list

Posted on 2004-09-10
17
876 Views
Last Modified: 2007-12-19
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
Comment
Question by:khairil
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 18

Assisted Solution

by:SjoerdVerweij
SjoerdVerweij earned 220 total points
ID: 12030041
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12030120
How would you like 1. formatted? Just a set of strings?
0
 
LVL 1

Expert Comment

by:biglaxman17
ID: 12030285
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12031618
Heh. Yes, and in SQL Server 2005 you can use a CTE.

Is it 2005 yet? :-)
0
 
LVL 13

Author Comment

by:khairil
ID: 12032154
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
 
LVL 1

Expert Comment

by:biglaxman17
ID: 12033092
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
 
LVL 13

Author Comment

by:khairil
ID: 12033130
emmm.. i still looking a solution in mssql stored proc. i hope somebody out there have the idea on doing these :(...
0
 
LVL 9

Expert Comment

by:solution46
ID: 12033393
khairil,

should have an answer for you shortly :)


SjoerdVerweij,

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



s46
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Expert Comment

by:solution46
ID: 12033406
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
 
LVL 13

Author Comment

by:khairil
ID: 12033524

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
 
LVL 9

Accepted Solution

by:
solution46 earned 220 total points
ID: 12033632
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
 
LVL 13

Author Comment

by:khairil
ID: 12033960
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
 
LVL 9

Expert Comment

by:solution46
ID: 12034166
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
 
LVL 13

Author Comment

by:khairil
ID: 12034337
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
 
LVL 13

Author Comment

by:khairil
ID: 12034435
emmm... i have download code project sample forum, they do have parent id but they implement sorting by on other field.
0
 
LVL 13

Author Comment

by:khairil
ID: 12034446
0
 
LVL 9

Expert Comment

by:solution46
ID: 12034506
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now