nested recordset problem

[wbodger]

So, I am trying to build a little report in three layers and I know there is a flaw in my logic, but I can't see it, so I was hoping one of you folks could help me out?

		<% Do Until rs.EOF %>
		<h1><%=rs("trip")%></h1>
			<%
			dim churchsql, churchrs
			churchsql = "SELECT l.id, l.church, l.age_group, l.trip, l.leader_name, l.leader_email, l.emerg_name, l.emerg_phone, l.emerg_relation, l.radiobutton, l.van_number, l.van_drivers "
			churchsql = churchsql & "FROM tbl_ss_group_leaders l JOIN tbl_ss_group_participants p ON l.id = p.ss_id "
			churchsql = churchsql & "WHERE p.processdate IS NULL "
			churchsql = churchsql & "AND trip = '" & rs("trip") & "'"

			SET churchrs = Conn.Execute(churchsql)
			%>
			<table border=0>
			<tr><td>church</td><td>age group</td><td>leader name</td><td>leader email</td><td>emergency contact</td><td>emergency contact phone</td><td>emergency contact relation</td><td>van agreement</td><td># van drivers</td><td>van drivers</td></tr>
			<% DO UNTIL churchrs.EOF %>
			<tr><td><%= churchrs("church") %></td><td><%= churchrs("age_group") %></td><td><%= churchrs("leader_name") %></td><td><%= churchrs("leader_email") %></td><td><%= churchrs("emerg_name") %></td><td><%= churchrs("emerg_phone") %></td><td><%= churchrs("emerg_relation") %></td><td><%= churchrs("radiobutton") %></td><td><%= churchrs("van_number") %></td><td><%= churchrs("van_drivers") %></td><tr>
			<tr><td></td>
				<td colspan="9">
				<%
				dim groupsql, grouprs
				groupsql = "SELECT p.name, p.email, p.gender, p.age, p.grade_comp "
				groupsql = groupsql & "FROM tbl_ss_group_participants p "
				groupsql = groupsql & "WHERE ss_id = " & churchrs("id")
	
				SET grouprs = Conn.Execute(groupsql)
				%>
					<table border=0">
					<tr><td>Name</td><td>Email</td><td>Gender</td><td>Age</td><td>Grade Completed</td></tr>
					<% DO UNTIL grouprs.EOF %>
					<tr><td><%= grouprs("name") %></td><td><%= grouprs("email") %></td><td><%= grouprs("gender") %></td><td><%= grouprs("age") %></td><td><%= grouprs("grade_comp") %></td></tr>
					<% grouprs.Movenext
					Loop%></table></td></tr>
			<% churchrs.Movenext 
			Loop %>
			</table>
		<% rs.MoveNext 
		Loop %>

So, I step into the first recordset (distinct trip names) just fine, then step into the second recordset selecting churches that have signed up for that trip. I get the recordset ok, but I iterate thru it way too many times (like as many times as participants). And then I take the id for each church and step into the third recordset where I choose all of the participants. So, if I had 1 church sign up for a trip to Belize and that church signed up 5 participants, then I am getting that listing of all 5 participants 5 times. They are in the correct trip for the correct church, they are just repeated as many times as there are participants.

I'm sure it is just a simple little thing I am missing, but I don't see it. Any help would be appreciated.

Thanks,
Willie

 

[wbodger]

And nevermind... It was a bad join in my SQL code, not sure why it took me so long to find it.

WB

 

[Remou]

At a guess I would say the problem is here:

[tt]churchsql = churchsql & "FROM tbl_ss_group_leaders l JOIN tbl_ss_group_participants p ON l.id = p.ss_id "[/tt]

Do you need to know that tbl_ss_group_participants processdate is null at this stage? If so, the query need to be constructed differently to prevent duplicates.

 

[wbodger]

Yeah, at this point I don't even need to join the participants table, my bad so I just removed the join and checked for a processdate on the leaders table.