Help with Query in ASP

[kmattera]

I am trying to do a one to many query to display data on a website but can't seem to figure this out.

I have 2 databases -- Teams and Contacts

Teams will have 20 rows with Team Name being one column and description as other. Team Name will come from a Lookup Combo Box.

Contacts will have 100's of rows since each Team will have multiple contacts. I again have used the "Team Name Lookup Combo Box" to tie in the relationship.

I can't figure out how to list the information via my SQL statement on my webpage.

I basically want it to resemble this:

BLUE TYPHOONS
Description - blah blah blah

Team Members
Mickey Mouse
Donald Duck
Goofy

PURPLE CHEETAHS
Description - blah blah blah

Team Members
Snow White
Dopey
Sleepy
Grumpy

But when I try to display the results, I'm getting a row for EACH team member including their Team info repeated each time.

Thanks for your help.

SELECT PU_Teams.ID, PU_Teams.[Team Name], PU_Teams.[Aim Statement], Contacts.ContactName, Contacts.ContactPhone, Contacts.ContactEmail
FROM PU_Teams INNER JOIN Contacts ON PU_Teams.ID = Contacts.Teams
GROUP BY PU_Teams.ID, PU_Teams.[Team Name], PU_Teams.[Aim Statement], Contacts.ContactName, Contacts.ContactPhone, Contacts.ContactEmail
ORDER BY PU_Teams.[Team Name];

 

[alex5935]

I am not sure if you have to do all in one join query, but you can
get desired output with two query and two loops.




sql="Select * from PU_TEAMS"
rs.execute(sql)
do while not rs.eof


'set teamid
pu_teamsid=rs("id")
print Team Name
print Description

sql="select * from Contacts where contact.Teams=" & pu_teamsID

rsmembers.execute(sql)

do while not rsmembers.eof
print rsmember("contactname")

next



next

This is not a clean code you want to check syntax for asp ado