[2005] select subquery

[jlbantang]

hi i created a simple subquery but im getting an execution error.

select parentID,childID, nodedescription
  (select nodedescription from tblMyTable order by nodedescription)
from tblMyTable
order by parentID

[jmcilhinney]

That's not valid SQL and I can't tell what it is you're trying to achieve from it. First you need to understand what subqueries are for. Like any query, a subquery creates a result set that contains records. You can think of a subquery as a temporary table and, as such, you can use it anywhere you could use a table. Could you possibly replace that subquery with a table name? Of course not. The SQL simply wouldn't make sense. So, what exactly are you trying to achieve? Maybe this is something you could have told us in the first place. If you want us to help you, don't you think it would be useful for us to know what you're trying to do?

[jlbantang]

my mistake.


sample table : tblmyTable
parentID - autonumber, childID, description

sample data table:

parentID childID description
1 0 Fruits
2 1 melon
3 1 apple
4 1 guava
5 0 cars
6 5 truck
7 5 pickup
8 1 banana

*note parent row has 0 childID value

i was trying to create a hierarchical tree output and the tsql should result like this:

Fruits
melon
apple
guava
banana
cars
truck
pickup

thanks.

[jmcilhinney]

First up, I think you've got your parent ID and child ID the wrong way around. multiple records could have the same parent, but they can't have the same child.

The output of a query is tabular, not hierarchical, so you can't create a hierarchical output that way. If you want to build a tree then you would have to read your tabular output in an appropriate way and build your tree from that. So assuming that you're using VB.NET, you might get all the data as is with a single query, then read it like this:

For Each parentRow As DataRow In myDataTable.Select("ParentID = 0")
    For Each childRow As DataRow in myDataTable.Select("ParentID = " & parentRow("ChildID"))
        MessageBox.Show(CStr(childRow("Description")), CStr(parentRow("Description")))
    Next childRow
Next parentRow