how to inner join tables

[gilgalbiblewhee]

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.

<%
'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.

[si_the_geek]

That looks right, this is the important bit:

FROM bible INNER JOIN biblewheel_url 
ON bible.book_spoke = biblewheel_url.book_spoke

What this means is that the records returned will be those with entries in both tables (INNER), and only where the "book_spoke" fields are identical in both (ON ...).


If you want to also show those which are in "bible" but not in "biblewheel_url", change the word INNER to LEFT.

[gilgalbiblewhee]

for example. Here's the result:
http://n.1asphost.com/wheelofgod/kjv...s&optAction=on
If you look at the column of: Book #/
Book Spoke
You'll notice all the links in the images are the same:
http://www.biblewheel.com/Wheel/Spokes/Aleph_God.asp
which is the first url in the biblewheel_url table.

[si_the_geek]

Could it be because they are all linking to the Book, rather than a specific chapter/verse?

Your SQL might need more joins, and you may need more data in your tables.

I would recommend trying a less popular search term (one that has mulitple books), so that you can see how it is working.


It might be useful if you let us know the structure of your tables (with a short demo set of data), and what you want returned by your SQL.

[gilgalbiblewhee]

Could it be because they are all linking to the Book, rather than a specific chapter/verse?

Your SQL might need more joins, and you may need more data in your tables.

I would recommend trying a less popular search term (one that has mulitple books), so that you can see how it is working.


It might be useful if you let us know the structure of your tables (with a short demo set of data), and what you want returned by your SQL.

I'll try to make a demo. My codes are long and too many include files.

I have 2 queries, or it might be a looping problem because the image (22 images of the Hebrew Alphabet) is linked to the first record's url, but doesn't recognize the others.

Ok. In a couple of days I'll try to post a simplified code. Thanks. But if you want to know what I mean, look at the images under the "Book #/
Book Spoke" column. If you put your mouse on one of the images in that column, you'll get a link saying Aleph, which is the link of the first letter of the hebrew alphabet, but this is tav the last letter of the hebrew alphabet. So I think the query's working but there might be a looping problem. And I don't know how to deal with loops.
http://n.1asphost.com/wheelofgod/kjv...s&optAction=on

[gilgalbiblewhee]

I don't know if this helps simplify. But I highlight where the problem seems to be. There are 2 queries.

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

[si_the_geek]

Actually it wasn't the code I wanted, it was the structure of the database (which I can see now is kjv.mdb).

Let us know what tables you have, what fields are in the tables, and some examples of data in each table, a bit like this:

Table:   People
ID   FirstName       Last Name
1    Joe              Bloggs
2    Fred             Thompson

Table:   Orders
PersonID      OrderDate         Price  ...
1             Dec 3 04         $10    
1             Nov 8 04         $5
2             Jan 4 05         $15

..and a brief example of what results you want out of it given this data, eg:

I want the details of the last order that each person placed, like this:

Name                  Date               Price
Joe Bloggs            Dec 3 04         $10 
Fred Thompson         Jan 4 05         $15

[gilgalbiblewhee]

Here it is:
http://n.1asphost.com/wheelofgod/tableinhtml.htm

[gilgalbiblewhee]

OK. I figured it out.

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.

[si_the_geek]

As I suspected you are linking purely on the book, whereas the data also specifies the chapter/verse (all of the data you have shown is book 1, chapter 1). As you have it, all of the data you have shown will return the first URL, as they are all in the same Book.

Without an example of the data you want returned I cant be sure, am I right in thinking that each Verse should have it's own url? (but should book/chapter have them too? or should they default to the first verse?)
The answer to this will help me determine what is actually required from your SQL.


If you do want each verse to have its own URL, what you should be joining on is:

a) all three fields [possibly not the best method, depending on what you want], eg:

SQL= "SELECT * "
SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
SQL = SQL & " ON biblewheel_url.book_spoke = bible.book_spoke "
SQL = SQL & " AND biblewheel_url.chapter_spoke = bible.chapter_spoke "
SQL = SQL & " AND biblewheel_url.verse_spoke = bible.verse_spoke "
SQL = SQL & " WHERE "

Please note that as the biblewheel_url table has the same data in book/chapter/verse you will only get one matching row (the first row of each table). If you change another row in the biblewheel_url table to have book=1, chapter=1, verse =2 then the URL for it will be returned with row 2 of the bible table.

b) the letter_id field (much better). You will need to add this to your Bible table to say which url should be returned for each row of data in the table. (if this is what you want, you can then remove the book/chapter/verse from the URL table).

[gilgalbiblewhee]

WHat I had in mind was to somehow link the 3 fieldnames to the proper numerical value's url. But before getting on o the 2nd and 3rd fields I wanted to test it on the 1st one as mentioned in the beginning of this thread.

Do you think "AND" will do the work? I'm thinking somehow it to has restart the query with the 2nd and 3rd fieldnames but without forgetting the previous queries.

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

then

SQL= "SELECT * "
SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
SQL = SQL & " ON biblewheel_url.chapter_spoke = bible.chapter_spoke "
SQL = SQL & " WHERE "

then

SQL= "SELECT * "
SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
SQL = SQL & " ON biblewheel_url.verse_spoke = bible.verse_spoke "
SQL = SQL & " WHERE "

because I would rather have it all on one line. Unless you have any suggestions...

[gilgalbiblewhee]

how about the "OR" instead of the "AND"?

[si_the_geek]

There are various options depending on what you want to achieve, and i'm afraid i still dont have a clue what you want.

Unless you explain it (with an example of some desired output) there isnt much I can do.

I am getting a very vague idea, but I dont want to keep on giving suggestions which arent relevant.

how about the "OR" instead of the "AND"?

it depends what you want, you may need and OR, a combination of AND/OR, a UNION, or combination of these.

[gilgalbiblewhee]

Ok. Here's an example (I hope the URL works).
http://n.1asphost.com/wheelofgod/kjv...s&optAction=on
Sorry it has Errors on the page (I think Javascript). I'll deal with that later. But if you look at "Book #/ Book Spoke" Book # represents the position of the book in the bible, whereas the Book Spoke is a little harder to explain it. It is numbered 001-022 according to the 22 letter of the Hebrew alphabet (that's why you'll see ) Hebrew letters. But to explain why all this is the REASON why I wanted to link with their appropriate URLs (Aleph to Tav). The same should apply to the chapter_spoke and verse_spoke.

One thing is that I have biblewheel_url.book_spoke as "primary key", and bible.id as the "pimary key". The tutorials have been pretty vague on how to associate 3 fieldnames of one table with the 3 fieldnames of the other table.

AN example of the tables in the db is shown in this link:
http://n.1asphost.com/wheelofgod/tableinhtml.htm

table: bible

id - number of the record in the table
book - book number in the bible
book_spoke - book spoke in the bible
recordType - for the checkbox list in the search page
book_title - name of the book
chap - chapter without 0 before (1, 2, 3 instead of 001, 002, 003)
chapter - chapters with 0 before (001, 002, 003 I did this because when I had 1, 2, 3...17 18, 19 when I searched "1" I would get results like: 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21...every number which has 1 in it in this column)
chapter_spoke - chapter spoke (like book_spoke and verse_spoke)
verse - verses of the bible
vers - same as "chap"
verse_spoke - same as "chapter"
text_data - text of that verse

Table: biblewheel_url

letter_id - number of record in the table
book_spoke - book spoke (I want the bible.book_spoke to match biblewheel_url.book_spoke to give the URL)
chapter_spoke (I want the bible.chapter_spoke to match biblewheel_url.chapter_spoke to give the URL)
verse_spoke (I want the bible.verse_spoke to match biblewheel_url.verse_spoke to give the URL)
url - linked to 22 urls

I hope I was clear in my explanation. My English is bad.

[si_the_geek]

Sorry for the delay, the "reply to post" email for this thread got stuck in the wrong part of my mailbox, and I forgot to come back to the thread

Ok, I think I understand what you want... Basically to join to the same table in 3 different ways (once per field).

To do this you need to use an Alias for each join, like this:

SQL= "SELECT bible.*, BookURL.URL, ChapterURL.URL, VerseURL.URL "
SQL = SQL & " FROM bible "
SQL = SQL & " LEFT JOIN biblewheel_url BookURL ON (BookURL.book_spoke = bible.book_spoke) "
SQL = SQL & " LEFT JOIN biblewheel_url ChapterURL ON (ChapterURL.chapter_spoke = bible.chapter_spoke) "
SQL = SQL & " LEFT JOIN biblewheel_url VerseURL ON (VerseURL.verse_spoke = bible.verse_spoke) "
SQL = SQL & " WHERE "

Is that right?