Could someone please tell me what the code below does. For example, is this returning the rows where Samplecategories is equal to parentid? Why do we have to have the NULL values and why do we need to know if parentid IS NULL?



Thanks In Advance

Private Sub PopulateRootLevel()

Dim objConn As New SqlConnection(_
"server=JOTEKE\SQLExpress;Trusted_Connection=true; DATABASE=TreeViewSampleDB")
Dim objCommand As New SqlCommand("select id,title,(select count(*) FROM SampleCategories " _
& "WHERE parentid=sc.id) childnodecount FROM SampleCategories sc where parentID IS NULL", _
objConn)

This is creating a SQL connection object and then a SQL command object. It's not doing anything else apart from that.

As far as your question about the SQL query itself:
It would appear that you have a self referencing categories table that defines a variable depth category tree. The query appears to be giving you a list of top level categories (i.e. ones with no [NULL] parent id) and the number of immediate child categories.

-Peter
compiledthoughts.com

dotnetDeveloper:

In addition to the response from planoie, my comments references the Query statement only and without know what fields are in the SampleCategories Table:

You are using a Nested SELECT-Statement. This means the inner SELECT will execute first. The inner SELECT will count all records or instances where the parentid = sc.id, if parentid is a reserved word. However it may also error because in your FROM clause the alias sc for SampleCategories IS NOT defined. When the alias is clearly defined you should get a TOTAL or Count of non-blank Categories and this value will be constant;

Looking at the outer SELECT it is not clear whether the childnodecount is a field in the SampleCategories table or a reserved word, but if it is you will get an error on that statement in the WHERE-clause because you have two different parent IDs: parentid and pareentID, decide which one you should use and be consistent. It seems to me, you are looking for those SampleCategories where you have BLANK Parent categories with Child/Children Node(s)or Dangling.

I hope this helps.


========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.