selecting records from multiple tables
Hi
i have a very simple database in ACCESS : with the following table
member_details : which has fields :
Member_id
Forename
Surname
home_number
mobile_number
i use the following to display results :
<%
strSql = "SELECT * FROM member_details order by " & sort_order
set rsConfig = my_Conn.Execute (strSql)
WHILE NOT rsConfig.EOF
%>
<TR BGCOLOR="<%=v_bgcolor%>">
<TD align="middle" class="boldtable"><a href="select_member.asp?<%=rsConfig("Member_ID")%> "> <%=rsConfig("Member_ID")%> </a></TD>
<TD align="middle"><%=rsConfig("Forename")%></TD>
<TD align="middle"><%=rsConfig("Surname")%></TD>
<TD align="middle"><%=rsConfig("Home_Number")%></TD>
<TD align="middle"><%=rsConfig("Mobile_Number")%></TD>
</TR>
<%
rsConfig.MoveNext
WEND
%>
the above code is cut down : i also have the ability to change the sort order depending on which column header is selected.
i now want to add another table called member_stats which will include
member_id
score
member_game_info.
i want to incorporate these results in the above results table : but for the results to be associated with the correct member_id dependant on the sort order ( i.e forename, surname or member_id ).
obviously this would be easy if i added these fields to my existing member_details table : but i want to know how to do this for the future.
do i have to do a subselect within the WHILE loop for this new table ? or is there a nice neat way of doing this?
thanks in advance
dave
|