Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 17th, 2003, 03:05 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I do hierarchical looping

I am a SQL novice. How do I do the following (I don't know if it matters, but I am using SQL 2000):

for each parent:
  display parent
  for each child of parent:
    display child
  end
end

In advance, THANKS!
SP

  #2 (permalink)  
Old December 17th, 2003, 05:16 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Do you only have a single parent-child relationship or is this recursive? There is a recent thread on this very topic.

Peter
------------------------------------------------------
Work smarter, not harder.
  #3 (permalink)  
Old December 17th, 2003, 06:38 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had looked briefly at the thread you mentioned, but didn't pursue it because it was talking about parent-child within the same table which is not what I have.

I have a parent table with multiple children tables. Each row in the parent table can have multiple children in each one of the children tables. I also have grandchildren tables, but assume the concept is the same.

Thanks!
SP

  #4 (permalink)  
Old December 17th, 2003, 10:21 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cairo, , Egypt.
Posts: 336
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alyeng2000
Default

do you want to display data using sql query, if so,
what is the parents and children you want to display,
what you meant is a simple Join for tables!!!

Ahmed Ali
Software Developer
  #5 (permalink)  
Old December 18th, 2003, 12:54 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I'm understanding joins, remember I'm new to this, I don't think it is what I want.

I want to display the parent and children records separately. For example:

Parent A
Child 1
Child 2
Child 3

Parent B
Child 1
Child 2
Child 3

What I don't know how to do is to get this result in SQL. The above example is what I would get from the pseudo-code:

For each Parent:
  Display Parent
  For each Child of Parent:
    Display Child
  End
End

SP

  #6 (permalink)  
Old December 18th, 2003, 01:06 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

What is the destination (consumer) of this query? Is the result of the query going to be used by some application (web site, client application)? That's makes a big difference on how you do what you are trying to do. If all you want is to be able to "Print" out the results within query analyzer, you need to use cursors to go thru the result set for each parent and then query the children.

Peter
------------------------------------------------------
Work smarter, not harder.
  #7 (permalink)  
Old December 18th, 2003, 01:10 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One is query analyzer, the other is a VB.NET dataset for use in both a Windows form app and an ASP.NET app.

Cursor? Not sure how to use one. Like I said, I'm new to SQL.

Thanks for your patience!
SP

  #8 (permalink)  
Old December 18th, 2003, 01:24 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

The method you need for query analyzer will be grossly different than what you would use for a .net dataset. In fact, for a dataset, you won't need any of this logic. You'll just return to datatables that are related and let the consuming application deal with how to organize the results.

Look up Cursors in SQL, I'm not really familiar with using them, because I work primarily with row sets. A cursor will allow you to do the SQL equivalent of a "For..Each".

Peter
------------------------------------------------------
Work smarter, not harder.
  #9 (permalink)  
Old December 18th, 2003, 01:31 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Peter, thanks for the info. I am new to VB.NET, too. This process, self-teaching, is quite difficult!

What I've been doing is executing a stored procedure to provide the result set to VB.NET app. Which is why I have my SQL question.

Figuring out how to do it has been hard.

Thanks,
SP
  #10 (permalink)  
Old December 18th, 2003, 02:08 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I understand your frustration. I have taught myself quite a bit.

It looks like you want to format the result in your SQL. This will be fine if you are running the queries in query analyzer, but once you want to get it in a consumer (client or web application) you will most certainly want to get the raw data and apply your own formatting that is suitable for the application UI.

Peter
------------------------------------------------------
Work smarter, not harder.




Similar Threads
Thread Thread Starter Forum Replies Last Post
flat vs. hierarchical style asker XML 0 March 28th, 2007 06:16 AM
transform from flat to hierarchical kkt XSLT 5 November 27th, 2006 02:15 AM
From flat to hierarchical seesharper XSLT 2 February 5th, 2006 06:48 AM
Multiple Hierarchical Databases Macsood Oracle 0 December 6th, 2005 12:47 PM
hierarchical display in datagrid aravwind General .NET 2 July 30th, 2004 03:12 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.