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

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.

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

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

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

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.

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

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.

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

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.