View Single Post
  #1 (permalink)  
Old March 4th, 2007, 01:28 PM
henry-horse henry-horse is offline
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Listing results from joined tables

Hi, hoping someone can help me with the following problem if I ask nicely!

I'm trying to set up a web page that lists a number of 'committees' and the members of each committee underneath the committee heading. I've set up three tables which hold the relevant info:

tbl_addbook_entries - contains the details for each person, name, email address etc.
tbl_addbook_committees - contains the name of each committee
tbl_addbook_committeemembers - a table containing two foreign keys to group committees and people together (one committee can have many members, one member can be in one or more committees)

I have written the asp code as follows to generate the committee 'breakdown' listing as follows:

Code:
'-- Connect to the database --
set conn = server.createobject("ADODB.Connection")
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

'-- Retrieve committee members and details --
sqlstring = "SELECT tbl_addbook_committees.order, tbl_addbook_committees.title, tbl_addbook_committees.role_title, tbl_addbook_entries_chairperson.name, tbl_addbook_entries_chairperson.surname, tbl_addbook_entries_chairperson.telephone, tbl_addbook_entries_chairperson.mobile, tbl_addbook_entries_chairperson.email, tbl_addbook_entries_chairperson.reveal_telephone, tbl_addbook_entries_chairperson.reveal_mobile, tbl_addbook_entries_chairperson.reveal_email, tbl_addbook_committeemembers.order, tbl_addbook_committeemembers.role_title, tbl_addbook_committeemembers.vacant, tbl_addbook_entries.name, tbl_addbook_entries.surname, tbl_addbook_entries.telephone, tbl_addbook_entries.mobile, tbl_addbook_entries.email, tbl_addbook_entries.reveal_telephone, tbl_addbook_entries.reveal_mobile, tbl_addbook_entries.reveal_email FROM (tbl_addbook_entries AS tbl_addbook_entries_chairperson INNER JOIN tbl_addbook_committees ON tbl_addbook_entries_chairperson.id = tbl_addbook_committees.chairperson) INNER JOIN (tbl_addbook_entries RIGHT JOIN tbl_addbook_committeemembers ON tbl_addbook_entries.id = tbl_addbook_committeemembers.member) ON tbl_addbook_committees.id = tbl_addbook_committeemembers.comittee ORDER BY tbl_addbook_committees.order, tbl_addbook_committeemembers.order"
set rsuser=server.createobject("ADODB.Recordset")
rsuser.open sqlstring, conn, 1, 2

'-- List the records from the address book one by one --
firstrecord = True
if (rsuser.eof <> True) then
  rsuser.movefirst
  while not rsuser.eof
    if ((rsuser("title")) = previous_title) then
      '-- Output HTML code for next Committee member if not the first --
      htmlcode = htmlcode & "<b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries.email") & "<br>"
      end if
      htmlcode = htmlcode & "<br>"
      response.write htmlcode
    else
      '-- If not first committee listed, output a horizontal line --
      if firstrecord <> True then
        response.write ""
      end if

      '-- Output HTML code for Committee title and chairperson --
      htmlcode = "<b>" & rsuser("title") & "</b><br><br>"
      htmlcode = htmlcode & "<b>Chairperson: " & rsuser("tbl_addbook_committees.role_title") & "</b><br>"
      htmlcode = htmlcode & "" & rsuser("tbl_addbook_entries_chairperson.name") & " " & rsuser("tbl_addbook_entries_chairperson.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries_chairperson.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries_chairperson.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries_chairperson.email") & "<br>"
      end if
      htmlcode = htmlcode & "<br>"
      response.write htmlcode

      '-- Output HTML code for first Committee member --
      htmlcode = htmlcode & "<b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries.email") & "<br>"
      end if
      htmlcode = htmlcode & "<br>"      
      response.write htmlcode
    end if  
    previous_title = rsuser ("title")
    rsuser.movenext
  wend
else
  response.write "<tr><td colspan=3>Sorry no records were found</td></tr>"
end if

'-- Close the database --
rsuser.close
set rsuser = nothing
conn.close
set conn = nothing
set x = nothing

%>
But the page displays the records like this:http://www.ahbpc.org.uk/calendar/addbook_preview.asp (i.e. for each record it repeats the details of all the previous records).

Can anyone shed any light on what I've done wrong? Most grateful for any pointers / help.

Many thanks
Pete

Reply With Quote