Re: how to inner join tables
That looks right, this is the important bit:
Code:
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.
Re: how to inner join tables
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.
Re: how to inner join tables
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.
Re: how to inner join tables
Quote:
Originally Posted by 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.
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
Re: how to inner join tables
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> + " &_
"<font color='blue'><b>" & Keywordb & "</b></font> + " &_
"<font color='green'><b>" & Keywordc & "</b></font> + " &_
"<font color='orange'><b>" & Keywordd & "</b></font> + " &_
"<font color='purple'><b>" & Keyworde & "</b></font> + " &_
"<font color='aqua'><b>" & Keywordf & "</b></font> " &_
" 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%> <%=Keywordb%> <%=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")%> <%=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 Spoke <%=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 Spoke <%=RS("chapter_spoke")%>"><br>
<%=RS("chapter_spoke")%></span>
</td>
<td width="10" align=center><span>
<IMG SRC="hebrew/<%=RS("verse_spoke")%>.jpg" ALT="Verse Spoke <%=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()%>
Re: how to inner join tables
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:
Code:
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:
Code:
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
Re: how to inner join tables
Re: how to inner join tables
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.
Re: how to inner join tables
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:
VB Code:
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).
Re: how to inner join tables
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.
Code:
SQL= "SELECT * "
SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
SQL = SQL & " ON biblewheel_url.book_spoke = bible.book_spoke "
SQL = SQL & " WHERE "
then
Code:
SQL= "SELECT * "
SQL = SQL & " FROM biblewheel_url INNER JOIN bible "
SQL = SQL & " ON biblewheel_url.chapter_spoke = bible.chapter_spoke "
SQL = SQL & " WHERE "
then
Code:
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...
Re: how to inner join tables
how about the "OR" instead of the "AND"?
Re: how to inner join tables
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.
Quote:
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.
Re: how to inner join tables
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
Quote:
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
Quote:
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.
Re: how to inner join tables
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 :blush:
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:
Code:
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?
