View Single Post
  #1 (permalink)  
Old February 16th, 2004, 03:04 PM
bridie bridie is offline
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Best method - Nested data?

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.
Reply With Quote