Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to inner join tables

    I have a MSAccess 2000 mdb where 2 tables must be connected with a fieldname "book_spoke".
    This is what I have so far. Is it right? The purpose is to find within the search result all the book_spoke s and link with the proper url for each book_spoke.
    Code:
    <%
    'Option Explicit
    'Response.End 
    Response.Buffer=false
    
    Dim letter
    Dim letterSQL
    Dim str
    letterSQL = "SELECT bible.book_spoke, "
    letterSQL = letterSQL & "bible.book_spoke, "
    letterSQL = letterSQL & "biblewheel_url.url "
    letterSQL = letterSQL & "FROM bible INNER JOIN biblewheel_url "
    letterSQL = letterSQL & "ON bible.book_spoke = "
    letterSQL = letterSQL & "biblewheel_url.book_spoke "
    '    letterSQL = "SELECT bible.book_spoke, "
    '    letterSQL = letterSQL & "biblewheel_url.url "
    '    letterSQL = letterSQL & "FROM biblewheel_url "
    '    letterSQL = letterSQL & "INNER JOIN bible "
    '    letterSQL = letterSQL & "ON biblewheel_url.book_spoke = "
    '    letterSQL = letterSQL & " bible.book_spoke "
    
        strConn = GetConnectionString()
    
    Set letter = Server.CreateObject("ADODB.Recordset")
        letter.CursorLocation = adUseClient
    
        letter.Open letterSQL, strConn, adOpenForwardOnly, adLockReadOnly
    str = str%>
    
    http://www.biblewheel.com
    <%=letter("url")%>
    
    <%
    
        letter.Close
        set letter = Nothing
    %>
    What I did was to make a parent child relationship within the "query" section in the mdb. But I'm not sure if I need to do other things.

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Your INNER JOIN statement is okay after the line of code letterSQL = letterSQL & "bible.book_spoke, " is removed. Did you get error message when you execute it?

    SELECT bible.book_spoke, biblewheel_url.url FROM bible INNER JOIN biblewheel_url ON bible.book_spoke = biblewheel_url.book_spoke

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    Unfortunately not. It's showing the url for the first record not the url I wanted.

    Code:
    letterSQL = "SELECT distinct biblewheel_url.url "
    letterSQL = letterSQL & " FROM biblewheel_url INNER JOIN bible "
    letterSQL = letterSQL & " ON biblewheel_url.book_spoke = bible.book_spoke"

  4. #4
    Join Date
    Jul 2004
    Posts
    494
    wait. Do I need to loop it?

  5. #5
    Join Date
    Jul 2004
    Posts
    494

    I don't know if this helps simplify.

    But I highlight where the problem seems to be. There are 2 queries.

    Code:
    <% 'Option Explicit
    Response.Buffer=false%>
    <%
    Const DB_NAME           = "kjv.mdb" ' Name of our database file
    Const INTRO             = "introduction to the wheelofgod.asp"
    Const THECYCLES         = "cycles.asp"
    Const SCRIPT_NAME       = "kjv.asp"  ' Name of this script
    Const SCRIPT_NAMES      = "kjvresp.asp"
    const SCRIPT_SAVED      = "saved.asp"
    Const SCRIPT_FEEDBACK   = "mailto.asp"
    Const SCRIPT_TEXT       = "bibletext.asp"
    
    Const RECORDS_PER_PAGE  = 10            ' Number of records per page
    
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdTableDirect = &H0200
    Const adUseClient = 3
    
    Private Function GetConnectionString()
        GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
                    "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                    "UID=;PWD=;"
    End Function
    Set Conn = server.createobject("ADODB.Connection")
    Conn.open GetConnectionString
    %>
    <%  Dim strConn     ' Database connection string
        Dim SQL         ' String that will have our SQL statments
        Dim RS          ' Recordset object
        Dim Keyword     ' Keyword for search
        Dim Keywordb
        Dim Keywordc
        Dim Keywordd
        Dim Keyworde
        Dim Keywordf
    
    'pageing
        Dim nRecCount   ' Number of records found
        Dim nPageCount  ' Number of pages of records we have
        Dim nPage       ' Current page number
    'query
        Dim iCounter
        Dim iLoopCount
        Dim aRecTypes
        Dim spoke       ' For dropdown
        Dim number
    
        Dim intRec
    
    SQL= "SELECT * from bible where "
    
        ' Let's see what user wants to search for today :)
        Keyword = Trim(Request.QueryString("Keyword"))
        Keywordb = Trim(Request.QueryString("Keywordb"))
        Keywordc = Trim(Request.QueryString("Keywordc"))
        Keywordd = Trim(Request.QueryString("Keywordd"))
        Keyworde = Trim(Request.QueryString("Keyworde"))
        Keywordf = Trim(Request.QueryString("Keywordf"))
        spoke = Request.Querystring("spoke")
        number = Request.QueryString("number")
    
    
    iCounter = 0
    
    If   request.QueryString("text_data")="yes" then
    
      SQL = SQL & "text_data LIKE '%" & Keyword & "%' AND "  
      SQL = SQL & "text_data LIKE '%" & Keywordb & "%' AND "
      SQL = SQL & "text_data LIKE '%" & Keywordc & "%' AND "  
      SQL = SQL & "text_data LIKE '%" & Keywordd & "%' AND "
      SQL = SQL & "text_data LIKE '%" & Keyworde & "%' AND "
      SQL = SQL & "text_data LIKE '%" & Keywordf & "%'"
    
      iCounter = iCounter + 1
    
    end if
    
    
    If   request.QueryString("book")="yes" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "book LIKE '" & number & "'"
    
      iCounter = iCounter + 1
    
    end if
    
    
    If   request.QueryString("book_title")="yes" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "book_title LIKE '%" & number & "%'"
    
      iCounter = iCounter + 1
    
    end if
    
    If   request.QueryString("chapter")="yes" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "chapter LIKE '%" & number & "%'"
    
      iCounter = iCounter + 1
    
    end if
    
    If   request.QueryString("verse")="yes" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "verse LIKE '%" & number & "%'"
    
      iCounter = iCounter + 1
    
    end if
    
    If   request.QueryString("book_spoke")="Book_Spoke" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "book_spoke = '" & spoke & "'"
    
      iCounter = iCounter + 1
    
    end if
    
    If   request.QueryString("chapter_spoke")="Chapter_Spoke" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "chapter_spoke = '" & spoke & "'"
    
      iCounter = iCounter + 1
    
    end if
    
    If   request.QueryString("verse_spoke")="Verse_Spoke" then
    
      If iCounter > 0 Then
        SQL = SQL & " AND "
      End If
    
      SQL = SQL & "verse_spoke = '" & spoke & "'"
    
      iCounter = iCounter + 1
    
    end if
    
    If Trim(Request.QueryString("recordType")) <> "" Then
    
    
      aRecTypes = Split(Request.QueryString("recordType"), ",")
    
      If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
        SQL = SQL & " AND ("
    
        For iLoopCount = 0 To UBound(aRecTypes)
          If iLoopCount <> 0 Then
            SQL = SQL & " OR "
          End If
    
          SQL = SQL & "recordType = '" & trim(aRecTypes(iLoopCount)) & "'"
        Next
      End If
      SQL = SQL & ")"
    End If
    
    SQL = SQL & " ORDER BY id ASC "
    %> <%
    set RS = Server.CreateObject("ADODB.Recordset")
    rs.PageSize= RECORDS_PER_PAGE
    rs.CursorLocation = adUseClient
    rs.CacheSize = 20
    RS.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly
    'RS.Open SQL, Conn, 3, 1, &H0001 ' Open recordset with appropriate parameters.
    rscount=rs.RecordCount
    rspage=rs.PageCount
    
    if request.querystring("page")="" then 
       page=1
    else
       page=cint((request.querystring("page")))
    end if
    %>
    <!--#include file=biblepagetop.txt-->
    <%
    If Not rs.EOF Then
    Response.Write "The King James Bible has " & rs.RecordCount &_
    " verses found matching ""<font color='red'><b>" & Keyword & "</b></font>&nbsp;+&nbsp;" &_ 
    "<font color='blue'><b>" & Keywordb & "</b></font>&nbsp;+&nbsp;" &_
    "<font color='green'><b>" & Keywordc & "</b></font>&nbsp;+&nbsp;" &_
    "<font color='orange'><b>" & Keywordd & "</b></font>&nbsp;+&nbsp;" &_
    "<font color='purple'><b>" & Keyworde & "</b></font>&nbsp;+&nbsp;" &_
    "<font color='aqua'><b>" & Keywordf & "</b></font>&nbsp;" &_
    " in Spoke <b>" & spoke & "</b>.<br>" 
    Response.Write "There are " & rs.PageCount & " page(s) of result(s).<br>"
    Response.Write "The current page is " & Page & ".<p>"
    End if%>
    <!--#include file=pagingrecordsets.asp-->
    <%If rs.BOF and rs.EOF Then%>
    <H2 align="center">We did not find a match of <i><b>"<%=Keyword%>&nbsp;<%=Keywordb%>&nbsp;<%=Keywordc%>"!</b></i></H2>
    <h5 align=center><A HREF="<%=SCRIPT_FEEDBACK%>">Feedback Forum</A>      |
    <A HREF="<%=SCRIPT_NAME%>">Back To Search Page</A></h5>
    <%Else%> 
    <table border="1" cellspacing="1" bgcolor="#0066CC">
    <tr style="height:12.75pt">
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">
     <form align="center" method="get" action="<%=SCRIPT_SAVED%>" id=form1 name=form1>Select</font><br>
     <select size="1" name="choices" id="choices">
            <option SELECTED VALUE="">None</option> 
            <option VALUE="001">Delete</option>
            <option VALUE="002">Email to:</option>
            <option VALUE="003">Print</option>
            <option VALUE="004">Save in:</option>
     </select><input type="submit" value="OK" name=submit1><br>
    <input type='checkbox' onclick='checkAll(this.form,this)' value="check all" name="ck<%CStr(id)%>">
     <font face="Verdana" color="#FFFFFF">Select All</font></th>
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book Title</font></th>
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book #/<br>Book Spoke</font></th>
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Chapter #/<br>Chapter Spoke</font></th>
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Verse #/<br>Verse Spoke</font></th>
     <th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Text</font></th>
      </tr>
       <%
    ' skip the dummy records
    if not rs.eof then
    rs.Move (page-1)*rs.pagesize
    end if
    if not rs.eof then
         ' Display the records
    for i=1 to rs.pagesize%> 
    <td width="100" nowrap rowspan="2" align=center BGCOLOR="#FFFFFF">
    <input type="checkbox" name="ck<%CStr(id)%> value="<%=rs("id")%>">
    <%=rs("id")%></span>
    </td>
    
    <td width="100" nowrap rowspan="2" align=center BGCOLOR="#FFFFFF">
    <%=rs("book_title")%></span>
    </td>
          
    <td width="100" align=center BGCOLOR="#FFFFFF">
    <%=rs("book")%></span>
    </td>
    
    <td width="100" nowrap align=center BGCOLOR="#FFFFFF">
    <%=rs("chapter")%></span>
    </td>
    
    <td width="100" nowrap align=center BGCOLOR="#FFFFFF">
    <%=rs("verse")%></span>
    </td>
    
    <td width="350" nowrap rowspan="2" align=left BGCOLOR="#FFFFFF"><font size='2' face="Verdana"> 
    <b><%=rs("book_title")%>&nbsp;&nbsp;<%=rs("chap")%>:<%=rs("vers")%></b><br>
    <!--#include file="highlight3.asp"-->
    </span>
    </font></td>
    </tr>
    
    <tr style="height:8.0pt"> 
    <td width="10" align=center><span>
    <center>
    <A href="
    
    <%
    'Option Explicit
    'Response.End 
    Response.Buffer=false
    
    Dim letter
    Dim letterSQL
    Dim str
    
    letterSQL = "SELECT distinct biblewheel_url.book_spoke, biblewheel_url.url "
    letterSQL = letterSQL & " FROM biblewheel_url INNER JOIN bible "
    letterSQL = letterSQL & " ON biblewheel_url.book_spoke = bible.book_spoke"
    
    
        strConn = GetConnectionString()
    
    Set letter = Server.CreateObject("ADODB.Recordset")
        letter.CursorLocation = adUseClient
    
        letter.Open letterSQL, strConn, adOpenForwardOnly, adLockReadOnly
    str = str%>
    
    http://www.biblewheel.com
    <%=letter("url")%>
    
    <%
    
        letter.Close
        set letter = Nothing
    %>
    " target="_blank">
    <IMG SRC="hebrew/<%=RS("book_spoke")%>.jpg" ALT="Book&nbsp;Spoke&nbsp;&nbsp;<%=RS("book_spoke")%>">
    </A><br>
    <%=RS("book_spoke")%>
    </center></span>
    </td>
    
    <td width="10" align=center><span>
    <IMG SRC="hebrew/<%=RS("chapter_spoke")%>.jpg" ALT="Chapter&nbsp;Spoke&nbsp;&nbsp;<%=RS("chapter_spoke")%>"><br>
    <%=RS("chapter_spoke")%></span>
    </td>
    
    <td width="10" align=center><span>
    <IMG SRC="hebrew/<%=RS("verse_spoke")%>.jpg" ALT="Verse&nbsp;Spoke&nbsp;&nbsp;<%=RS("verse_spoke")%>"><br>
    <%=RS("verse_spoke")%></span>
    </td>
    </tr>
       <%rs.movenext
          ' Exit the loop when reaching the end of the recordset
    If rs.EOF Then Exit For 'end if
    next
    end if%> 
     </table>
    <%end if%> 
    <!--#include file=pagingrecordsets.asp--> 
    <!--#include file=biblepagebottom.txt-->
    <% rs.Close()%>

  6. #6
    Join Date
    Jul 2004
    Posts
    494

    OK. I figured it out.

    Code:
    SQL= "SELECT * "
    SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
    SQL = SQL & " ON biblewheel_url.book_spoke = bible.book_spoke "
    SQL = SQL & " WHERE "

    Now biblewheel_url.book_spoke is the primary key. It is numbered 001-022 with different URLs.
    http://n.1asphost.com/wheelofgod/tableinhtml.htm
    But withinthe biblewheel_url table there are chapter_spoke and verse_spoke fieldnames too numbered 001-022 is it possible that there is a SQL statement which identifies with the URL as shown in the link table:biblewheel_url.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •