Hi,
I have tried to search for this, but haven't been able to find anything that exactly fits my situation.

I have data from several tables that I need to display. The information is nested, so I have it working using nested loops, but I don't think nesting the DB connections is the best way to do this.

Here is a quick example of the data I have.

Soldier Table:
ID, Name, RankID, PlatoonID

Rank Table:
ID, RankName, RankImage, RankOrderVal

Platoon Table:
ID, PlatoonName, PlatoonDesc, PlatoonOrderVal

Medal Table:
ID, MedalName, MedalIMG, MedalDesc, MedalOrderVal

Award Table:
ID, AwardName, AwardIMG, AwardDesc, AwardOrderVal

SoldierMedals:
ID, UserID, MedalID, Comment

SoldierAwards:
ID, UserID, AwardID, Comment


Here is what I have so far.

DB Connect 1
Select1 "Platoon"
    Loop 1
     Output PlatooNames
     DB Connect 2
     Select 2 "Rank"
     Loop2
     Output Ranks
     DB Connect 3
     Select 3 "Soldiers"
         Loop 3
             Output Soldiers
             DB Connect 4
             Select 4 "Medals"
         Loop 4
                 Output medals
             End Loop 4
             Close DB 4
             DB Connect 5
             Select 5 "Awards"
             Loop 5
                 Output Awards
             End Loop 5
             Close DB 5
         End Loop3
         Close DB 3
     End Loop 2
    Close DB 2
End Loop 1
Close DB 1

Here is a sample output:

Platoon 1
Captains
     Soldier 3 - Medals 1, 2, 3
     Soldier 1 - Medals 2, 4 - Awards 4
Privates
     Soldier 5 - Medals 5, 6 - Awards 2
     Soldier 4 - Medals None

Platoon 2
Corperal
     Soldier 7 - Medals 3
     Soldier 2 - Medals 1 - Awards 2
Staff Sgt
     Soldier 6 - Medals 7

As you can see, some soldiers can have multiple medals, or none.

Sorry for all the info, but I wanted to be clear about what I was doing.

So, is there a better way to do this?

Bridie.

Making multiple selects like you are doing is never the best way. If you have to make calls over and over to the database then you are going to see a real big hit in performance. The way I usually do something like this is to use a bunch of joins in the data to change relational data (best way to store it in the DB) to a "flat table". Then in your asp code you determine when the data changes within each level. You have to store the "last item" for each level you are working with. So you have a single loop that goes thru each row returned and you have several If structures that check for a new occurance of each level.

For Each row
    If row("platoon") <> sLastPlatoon Then
        'output platoon text
    End If
    If row("rank") <> sLastRank Then
        'output rank text
    End If
    ....
Next

Although this is a little bit more clumsy, it's a whole lot more efficient than make repeated calls to the database. The storage you have for the data is quite correct, you just need to select it for processing in a different way that is more efficient for the data consumer (ASP). Post back if you need a hand constructing a query to give you a "flat file" structure.

Peter
------------------------------------------------------
Work smarter, not harder.

Alternatively, you could use Data Shaping. It's a bit scary technology, with an odd syntax, but it comes in handy every now and then.

http://www.planet-source-code.com/vb...=6512&lngWId=4

http://msdn.microsoft.com/library/de...pingdetail.asp

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi,
  Thanks for your help.. I don't think I am ready for data shaping yet.

I'll give the flat table method a try.

Thanks,
Bridie

Try joining your tables within a select statement and as you loop (once) you can get these results by ordering your output properly and passing a variable for each group which will allow you to supress the duplicates. It's pretty simple and will give you exactly what you are looking for. If the info you provided allows me to, I will come up with a solution when I get some time today.

By the way, where you say Medals 7, does 7 represent a count of how many medals this soldier has or is it a certain medal named 7? If it is a name and you have Medals 1, 2, 3, then it is going to be more difficult trying to avoid any nested recordsets. In this case I would nest a recordset for Medals and Awards.