I have many tables in an MS SQL database, i use ASP VB coding.

We run a villa rental web site, thus have a table of properties. In this table we store a drill down as to the location, eg country > subregion1 > subregion 2 etc. These countries and sub region are located in look up tables, which are joined by

country | countryID
SubRegion1ID | subregion1Name | CountryID
SubRegion2ID | subregion2Name | CountryID | SubRegion1ID
Etc

-------

We want our search pages to list all the active properties (where AdStatus = '1', no problem) by country, then by region. And also list how many properties are in each sub region, down to subregion1.

We also want conditional statements that if there is no property in a particular subregion then it will not display it on the site. - This shouldnt be a problem once I know how to do the above!

------

Does anyone have any ideas how to tackle this. At present we have to do it manually and step through each record to check where it is, add this to a variable and then place the variable next to the name. However if we add a sub region to the database this means we need to add it to all the pages on the web site.

We want a dynamic solution what will allow us to drill down each location, detailing each time how many properties are active in each region.

-----------

dont think i said enough about the databse design so here goes!

there are 7 tables we need to worry about...

tblProperties
- propertID
- ownerID
- AdStatus
- country
- subregion1
- subregion2
- subregion3
- subregion4

tblOwner
- ownerID
- Name, Address, Etc

inthemed_countries
-countryID
-countryName

inthemed_subregion1
-subregion1ID
-countryID
-subregion1Name

Etc

So the countries and regions are simply look up tables, which then store the exact location in the tblProperties.

There is no actual input of data into the country tables.

However what I need to do i step through all the country tables, and if there is a property, which is active, then it needs to..

1. show that region
2. start counting how many properties are in that country /region

---------
hope this is a bit more helpful!

btw - I dont know much about joins, so will have to so some research, I am a bit of a point an click coder when it comes to SQL, other VB functions I am ok, but I know this is down to how to structure the intital recordset query.

thanks again!