how to inner join tables

gilgalbiblewhee · #1 (**permalink**) 01-27-05, 13:05

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.

gyuan · #2 (**permalink**) 01-28-05, 21:31

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

gilgalbiblewhee · #3 (**permalink**) 01-29-05, 21:34

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"

gilgalbiblewhee · #4 (**permalink**) 01-29-05, 21:36

wait. Do I need to loop it?

gilgalbiblewhee · #5 (**permalink**) 01-31-05, 12:32

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()%>

gilgalbiblewhee · #6 (**permalink**) 01-31-05, 19:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech