Nested Query on Parent/Child table

This tutorial will explain the use of a nested sql query with the command COUNT in conjuction with a Parent/Child table. You can use this as a method to get the number of records within a certain category of two tables related with each other (MainCategory= Parent table and SubCategory=Child table).

There is also an Add Link page in the tutorial, so you can add a link and see the results. This tutorial presumes that you use MS SQL and a program that can generate tables and relation-ships to tables (for example: Enterprise Manager)

The example that i used for this tutorial is a links main page which holds the MainCategories and goes to the detail page which contains the actual links related to that MainCategory.

First of all we start building two tables, one for the MainCategory and one for the SubCategory.

MainCategory Table

With the SubCatgory table we have to take care of a relationship, so that the two tables can communicate. We do this by using the Primary Key of the MainCatgory table.

SubCategory Table

Relationship

Many-to-One

In order to create this relationship, you right-click on the SubCategory table and choose Design Table. A new screen pops-up and then you choose the relationship button (where the mouse is pointing).

Once you are done with this, you can start make the actual MainPage, LinkPage and AddLink Page.

First of all we make or choose a connection to the MS SQL database (DSN or Custom Connection String).

MainPage

Here we come directly to the nested query topic.

So, here is what this does:

Actually if we leave out the nested query, all we have is the following.

SELECT * FROM dbo.udMainCat

The get the records that relate to this MainCategory, we add the nested query.

SELECT COUNT(*) FROM dbo.udSubCat WHERE dbo.udSubCat.MCAT = dbo.udMainCat.MCAT) AS TOTAL_LINKS

These two sql commands together.

SELECT *, (SELECT COUNT(*) FROM dbo.udSubCat WHERE dbo.udSubCat.MCAT = dbo.udMainCat.MCAT) AS TOTAL_LINKS
FROM dbo.udMainCat

What we say here is; count the records in the table udSubCat where the relation is equal to the key MCAT (relationship we have created) and call this field TOTAL_LINKS.

This is what your Data Binding should look like:

Continued on page 2