Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > I used your query and devised this which works: > > SELECT * FROM Bible WHERE ID BETWEEN > (SELECT ID FROM Bible WHERE Book=64 AND Chapter=1 AND Verse=1) > AND > (SELECT ID FROM Bible WHERE Book=66 AND Chapter=1 AND Verse=3); > You might also want to redefine your table so it reflects really what is stored in it. The least unit of distinction in your table is a verse, so, since the plural of verse is verses I would call the table "verses" versus Bible which is worse (hmmm). CREATE TABLE verses ( id INTEGER PRIMARY KEY, verse INTEGER, chapter INTEGER, book INTEGER ); assuming you use only numbers to identify the above entities. Note, redefining this way won't affect your query -- it will only make your programming simpler and easier to understand. Remember, you table really contains "verses," not "Bible." An additional strategy would be to create a composite primary key out of book, chapter, and verse, which is exactly what Igor and Richard suggested, but multiplying Bible by 1000_000 and Chapter by 1000, and so on. That would be kinda like the new sqlite versioning system of 3070400. Just create a style, and stick to it. > On Mon, Dec 13, 2010 at 6:25 PM, Puneet Kishor > wrote: > >> >> steve mtangoo wrote: >>> No problem. >>> Now only If I could get the ID of the limits (that is ID for Book 1 >> Chapter >>> 1 and Verse 1 for example) and the end limit, my problem will be >>> solved! >> Well, that is simple... >> >> SELECT ID >> FROM Bible >> WHERE Book = 1 AND Chapter = 1 AND Verse = 1 >> >> same for the ending verse. >> >> In fact, as you possibly noted, if the ID is sequentially assigned to >> each verse (assuming, the verses have been inserted in the desired >> order), you could just find out the IDs of the lower and higher verses, >> and then use those to find all the verses in between. >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 12/13/2010 11:17 AM, Stefano Mtangoo wrote: > Thanks Dr. and I'm checking the zip file. But to be frank, I don't > understand the calculation done below. How do I come to such claculation > (excuse my ignorance)? also how do I query that simple BETWEEN? > That seems to be easiest way but I haven't grasped it yet > Thanks > > On Mon, Dec 13, 2010 at 9:02 PM, Richard Hipp wrote: > >> id = book_number*100 + chapter*1000 + verse. The actual book is arranged in a hierarchy: books, then chapters, then verses. The above flattens the hierarchy in a way that preserves ordering: all verses in Genesis (1M <= v < 2M) have numbers smaller than any verse in Exodus (2M <= v < 3M), etc. Think of it like a phone number: the first few digits give the country, the next few the area, and the last several identify whoever you're trying to call. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Thanks Dr. and I'm checking the zip file. But to be frank, I don't understand the calculation done below. How do I come to such claculation (excuse my ignorance)? also how do I query that simple BETWEEN? That seems to be easiest way but I haven't grasped it yet Thanks On Mon, Dec 13, 2010 at 9:02 PM, Richard Hipp wrote: > > >id = book_number*100 + chapter*1000 + verse. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On Mon, Dec 13, 2010 at 9:04 AM, Puneet Kishor wrote: > > > steve mtangoo wrote: > > I have a script that is supposed to query the Bible scriptures between > two > > intervals. My table is named Bible and have columns: ID (int), Book > (int), > > Chapter(int), Verse (int) and Scripture(text). > > > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > > verses. Now suppose I want to get scripture between book 1 chapter 1 > verse 1 > > and book 2 chapter 3 Verse 1, how do I go about? > Psalms has the most chapters (150) and Psalm 119 has the most verses (191). So the chapter number and the verse number is always well less than 1000. So you could encode each verse using a single integer (hint: making it the INTEGER PRIMARY KEY): id = book_number*100 + chapter*1000 + verse. The extracting a range of verses is a simple BETWEEN on the ID field. And the ID also works nicely as the DOCID for a full-text query. This the approach taken in the SQLite database contained in the following ZIP file: http://www.sqlite.org/kjvbible_sqlite.zip > > > SELECT Scripture > FROM Bible > WHERE > Book > 0 AND Book < 3 AND > Chapter > 0 AND Chapter < 4 AND > Verse > 0 AND Verse < 2 > > > My knowledge of SQLite3 > > have taken me to a dead end! > > You need to acquire knowledge of SQL, not sqlite3. Google for tutorials. > > > > > You can check how bible have its chapters and verses arranged at > > www.biblegateway.com. > > > > Thanks! > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Puneet Kishor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
I would like to thank all of you who helped me to achieve this. It took me a lot of time trying to figure out, but you guys have helped me a lot I will stick with mailing list! Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Hahaha! A coincidence! Thanks! On Mon, Dec 13, 2010 at 6:30 PM, Gerry Snyder wrote: > On 12/13/2010 7:55 AM, steve mtangoo wrote: > > Yes, ID is autoincrement and hence sequential. > > The problem with using it is, I have to know the ID of the beginning (for > eg > > Book 1 Chapter 1 Verse 2) and the Id of the end (eg Book 4 Chapter 10 > Verse > > 3). Then Simple BETWEEN will resolve it. > > > > Thanks for replying! > > > > select verse from Bible where ID between (select ID from Bible where > book = 1 and Chapter = 1 and Verse = 1) and (select ID from Bible where > book = 2 and Chapter = 3 and Verse = 1) > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
I used your query and devised this which works: SELECT * FROM Bible WHERE ID BETWEEN (SELECT ID FROM Bible WHERE Book=64 AND Chapter=1 AND Verse=1) AND (SELECT ID FROM Bible WHERE Book=66 AND Chapter=1 AND Verse=3); On Mon, Dec 13, 2010 at 6:25 PM, Puneet Kishor wrote: > > > steve mtangoo wrote: > > No problem. > > Now only If I could get the ID of the limits (that is ID for Book 1 > Chapter > > 1 and Verse 1 for example) and the end limit, my problem will be solved! > > Well, that is simple... > > SELECT ID > FROM Bible > WHERE Book = 1 AND Chapter = 1 AND Verse = 1 > > same for the ending verse. > > In fact, as you possibly noted, if the ID is sequentially assigned to > each verse (assuming, the verses have been inserted in the desired > order), you could just find out the IDs of the lower and higher verses, > and then use those to find all the verses in between. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 12/13/2010 7:55 AM, steve mtangoo wrote: > Yes, ID is autoincrement and hence sequential. > The problem with using it is, I have to know the ID of the beginning (for eg > Book 1 Chapter 1 Verse 2) and the Id of the end (eg Book 4 Chapter 10 Verse > 3). Then Simple BETWEEN will resolve it. > > Thanks for replying! > select verse from Bible where ID between (select ID from Bible where book = 1 and Chapter = 1 and Verse = 1) and (select ID from Bible where book = 2 and Chapter = 3 and Verse = 1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > No problem. > Now only If I could get the ID of the limits (that is ID for Book 1 Chapter > 1 and Verse 1 for example) and the end limit, my problem will be solved! Well, that is simple... SELECT ID FROM Bible WHERE Book = 1 AND Chapter = 1 AND Verse = 1 same for the ending verse. In fact, as you possibly noted, if the ID is sequentially assigned to each verse (assuming, the verses have been inserted in the desired order), you could just find out the IDs of the lower and higher verses, and then use those to find all the verses in between. > Thanks for helping! > > On Mon, Dec 13, 2010 at 6:13 PM, Puneet Kishor wrote: > >> Yes, of course, you are correct. Goes on to show that mapping what one >> thinks in the mind (a logical query) to SQL (a set query) can be nastily >> difficult. >> >> Thanks for the correction. >> >> -- >> Puneet Kishor http://punkish.org >> Carbon Model http://carbonmodel.org >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org >> Science Fellow >> http://creativecommons.org/about/people/fellows#puneetkishor >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu >> --- >> Assertions are politics; backing up assertions with evidence is science >> === >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
No problem. Now only If I could get the ID of the limits (that is ID for Book 1 Chapter 1 and Verse 1 for example) and the end limit, my problem will be solved! Thanks for helping! On Mon, Dec 13, 2010 at 6:13 PM, Puneet Kishor wrote: > > Yes, of course, you are correct. Goes on to show that mapping what one > thinks in the mind (a logical query) to SQL (a set query) can be nastily > difficult. > > Thanks for the correction. > > -- > Puneet Kishor http://punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Fellow > http://creativecommons.org/about/people/fellows#puneetkishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > select * from Bible where (book> :1 or (book=:1 and (chapter>:1 or > (chapter=:1 and verse>=:1) and (book< :3 or (book=:3 and (chapter<:1 or > (chapter=:1 and verse<=:1); > Does not work. Is there any issue Yes, don't use :1. Just use the number 1. SELECT * FROM Bible WHERE ( book > 1 OR ... and so on ) > > On Mon, Dec 13, 2010 at 6:11 PM, Puneet Kishor wrote: > >> >> steve mtangoo wrote: >>> Hi Igor, >>> would you explain what are bookLow and chapterHigh? >>> Do you mean lowest book and highest chapter? >> :bookLow and :chapterHigh are place holders for numbers you will >> provide. Those numbers stand for the lowest numbered book and the >> highest numbered chapter between which you want to search. >> >> >>> I want to test this query but please help me to understand it. >>> select * from Bible where >>> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or >>> (chapter=:chapterLow and verse>=:verseLow) >>> and >>> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or >>> (chapter=:chapterHigh and verse<=:verseHigh); >>> >>> And thanks to everybody who is helping here! >>> >>> On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik >> wrote: steve mtangoo wrote: > I have a script that is supposed to query the Bible scriptures between two > intervals. My table is named Bible and have columns: ID (int), Book (int), > Chapter(int), Verse (int) and Scripture(text). > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so >> do > verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 > and book 2 chapter 3 Verse 1, how do I go about? My knowledge of >> SQLite3 > have taken me to a dead end! Here's a straighforward answer: select * from Bible where (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or (chapter=:chapterLow and verse>=:verseLow) and (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or (chapter=:chapterHigh and verse<=:verseHigh); Here's a trick to make it shorter: select * from Bible where book*100 + chapter*1000 + verse between :bookLow*100 + :chapterLow*1000 + :verseLow and :bookHigh*100 + :chapterHigh*1000 + :verseHigh; Basically, the idea is to assign each verse a unique number (the query above assumes there are no more than 1000 verses per chapter and >> chapters per book; adjust multipliers if there are more). You may want to >> consider working with such sequential numbers internally in your program, >> converting between them and (book, chapter, verse) format on input/output only. So, define your table as Bible(VerseNumber integer primary key, Scripture >> text), and the query becomes simply select * from Bible where VerseNumber between :VerseLow and :VerseHigh; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -- >> Puneet Kishor http://punkish.org >> Carbon Model http://carbonmodel.org >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org >> Science Fellow >> http://creativecommons.org/about/people/fellows#puneetkishor >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu >> --- >> Assertions are politics; backing up assertions with evidence is science >> === >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
select * from Bible where (book > :1 or (book=:1 and (chapter>:1 or (chapter=:1 and verse>=:1) and (book < :3 or (book=:3 and (chapter<:1 or (chapter=:1 and verse<=:1); Does not work. Is there any issue On Mon, Dec 13, 2010 at 6:11 PM, Puneet Kishor wrote: > > > steve mtangoo wrote: > > Hi Igor, > > would you explain what are bookLow and chapterHigh? > > Do you mean lowest book and highest chapter? > > :bookLow and :chapterHigh are place holders for numbers you will > provide. Those numbers stand for the lowest numbered book and the > highest numbered chapter between which you want to search. > > > > > > I want to test this query but please help me to understand it. > > select * from Bible where > > (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or > > (chapter=:chapterLow and verse>=:verseLow) > > and > > (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or > > (chapter=:chapterHigh and verse<=:verseHigh); > > > > And thanks to everybody who is helping here! > > > > On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik > wrote: > > > >> steve mtangoo wrote: > >>> I have a script that is supposed to query the Bible scriptures between > >> two > >>> intervals. My table is named Bible and have columns: ID (int), Book > >> (int), > >>> Chapter(int), Verse (int) and Scripture(text). > >>> > >>> Now the books are unique i.e. 1-66 but chapters keep repeating, and so > do > >>> verses. Now suppose I want to get scripture between book 1 chapter 1 > >> verse 1 > >>> and book 2 chapter 3 Verse 1, how do I go about? My knowledge of > SQLite3 > >>> have taken me to a dead end! > >> Here's a straighforward answer: > >> > >> select * from Bible where > >> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or > >> (chapter=:chapterLow and verse>=:verseLow) > >> and > >> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or > >> (chapter=:chapterHigh and verse<=:verseHigh); > >> > >> Here's a trick to make it shorter: > >> > >> select * from Bible where > >> book*100 + chapter*1000 + verse between > >> :bookLow*100 + :chapterLow*1000 + :verseLow > >> and > >> :bookHigh*100 + :chapterHigh*1000 + :verseHigh; > >> > >> Basically, the idea is to assign each verse a unique number (the query > >> above assumes there are no more than 1000 verses per chapter and > chapters > >> per book; adjust multipliers if there are more). You may want to > consider > >> working with such sequential numbers internally in your program, > converting > >> between them and (book, chapter, verse) format on input/output only. So, > >> define your table as Bible(VerseNumber integer primary key, Scripture > text), > >> and the query becomes simply > >> > >> select * from Bible where VerseNumber between :VerseLow and :VerseHigh; > >> > >> -- > >> Igor Tandetnik > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Puneet Kishor http://punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Fellow > http://creativecommons.org/about/people/fellows#puneetkishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Igor Tandetnik wrote: > Puneet Kishor wrote: >> steve mtangoo wrote: >>>I have a script that is supposed to query the Bible scriptures between >>> two >>> intervals. My table is named Bible and have columns: ID (int), Book (int), >>> Chapter(int), Verse (int) and Scripture(text). >>> >>> Now the books are unique i.e. 1-66 but chapters keep repeating, and so do >>> verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 >>> and book 2 chapter 3 Verse 1, how do I go about? >> >> SELECT Scripture >> FROM Bible >> WHERE >> Book> 0 AND Book< 3 AND >> Chapter> 0 AND Chapter< 4 AND >> Verse> 0 AND Verse< 2 > > This excludes, for example, book 1 chapter 10. I'm pretty sure the OP wanted > it included. Yes, of course, you are correct. Goes on to show that mapping what one thinks in the mind (a logical query) to SQL (a set query) can be nastily difficult. Thanks for the correction. -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > Hi Igor, > would you explain what are bookLow and chapterHigh? > Do you mean lowest book and highest chapter? :bookLow and :chapterHigh are place holders for numbers you will provide. Those numbers stand for the lowest numbered book and the highest numbered chapter between which you want to search. > > I want to test this query but please help me to understand it. > select * from Bible where > (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or > (chapter=:chapterLow and verse>=:verseLow) > and > (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or > (chapter=:chapterHigh and verse<=:verseHigh); > > And thanks to everybody who is helping here! > > On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik wrote: > >> steve mtangoo wrote: >>> I have a script that is supposed to query the Bible scriptures between >> two >>> intervals. My table is named Bible and have columns: ID (int), Book >> (int), >>> Chapter(int), Verse (int) and Scripture(text). >>> >>> Now the books are unique i.e. 1-66 but chapters keep repeating, and so do >>> verses. Now suppose I want to get scripture between book 1 chapter 1 >> verse 1 >>> and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQLite3 >>> have taken me to a dead end! >> Here's a straighforward answer: >> >> select * from Bible where >> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or >> (chapter=:chapterLow and verse>=:verseLow) >> and >> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or >> (chapter=:chapterHigh and verse<=:verseHigh); >> >> Here's a trick to make it shorter: >> >> select * from Bible where >> book*100 + chapter*1000 + verse between >> :bookLow*100 + :chapterLow*1000 + :verseLow >> and >> :bookHigh*100 + :chapterHigh*1000 + :verseHigh; >> >> Basically, the idea is to assign each verse a unique number (the query >> above assumes there are no more than 1000 verses per chapter and chapters >> per book; adjust multipliers if there are more). You may want to consider >> working with such sequential numbers internally in your program, converting >> between them and (book, chapter, verse) format on input/output only. So, >> define your table as Bible(VerseNumber integer primary key, Scripture text), >> and the query becomes simply >> >> select * from Bible where VerseNumber between :VerseLow and :VerseHigh; >> >> -- >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Yes, ID is autoincrement and hence sequential. The problem with using it is, I have to know the ID of the beginning (for eg Book 1 Chapter 1 Verse 2) and the Id of the end (eg Book 4 Chapter 10 Verse 3). Then Simple BETWEEN will resolve it. Thanks for replying! On Mon, Dec 13, 2010 at 5:46 PM, Gerry Snyder wrote: > On 12/13/2010 6:54 AM, steve mtangoo wrote: > > I have a script that is supposed to query the Bible scriptures between > two > > intervals. My table is named Bible and have columns: ID (int), Book > (int), > > Chapter(int), Verse (int) and Scripture(text). > > Is your ID column a sequential numbering of the verses? If so, using it > could simplify the query. > > > Gerry > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 12/13/2010 6:54 AM, steve mtangoo wrote: > I have a script that is supposed to query the Bible scriptures between two > intervals. My table is named Bible and have columns: ID (int), Book (int), > Chapter(int), Verse (int) and Scripture(text). Is your ID column a sequential numbering of the verses? If so, using it could simplify the query. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Hi Igor, would you explain what are bookLow and chapterHigh? Do you mean lowest book and highest chapter? I want to test this query but please help me to understand it. select * from Bible where (book > :bookLow or (book=:bookLow and (chapter>:chapterLow or (chapter=:chapterLow and verse>=:verseLow) and (book < :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or (chapter=:chapterHigh and verse<=:verseHigh); And thanks to everybody who is helping here! On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik wrote: > steve mtangoo wrote: > > I have a script that is supposed to query the Bible scriptures between > two > > intervals. My table is named Bible and have columns: ID (int), Book > (int), > > Chapter(int), Verse (int) and Scripture(text). > > > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > > verses. Now suppose I want to get scripture between book 1 chapter 1 > verse 1 > > and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQLite3 > > have taken me to a dead end! > > Here's a straighforward answer: > > select * from Bible where > (book > :bookLow or (book=:bookLow and (chapter>:chapterLow or > (chapter=:chapterLow and verse>=:verseLow) > and > (book < :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or > (chapter=:chapterHigh and verse<=:verseHigh); > > Here's a trick to make it shorter: > > select * from Bible where > book*100 + chapter*1000 + verse between >:bookLow*100 + :chapterLow*1000 + :verseLow > and >:bookHigh*100 + :chapterHigh*1000 + :verseHigh; > > Basically, the idea is to assign each verse a unique number (the query > above assumes there are no more than 1000 verses per chapter and chapters > per book; adjust multipliers if there are more). You may want to consider > working with such sequential numbers internally in your program, converting > between them and (book, chapter, verse) format on input/output only. So, > define your table as Bible(VerseNumber integer primary key, Scripture text), > and the query becomes simply > > select * from Bible where VerseNumber between :VerseLow and :VerseHigh; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
May be I misrepresented myself. I know SQL but this seems to be SQLite3 specific thing. The query below does not work and the reason is Chapters and verses are repetitive. For example Book 1 will have chapters 1, 2, 3, 4, 5 and then Book 2 will have 1, 2, 3, 4, 5 , 6, 7 and then Book 3 will have 1, 2, 3, 4, 5 . I asked it in general SQL language forum and something said in MySQl there is something called row constructs SELECT * FROM bible WHERE (1,1,3) <= (book, chapter, verse) AND (book, chapter, verse) <= (1,2,2) See the link with the same question as mine http://stackoverflow.com/questions/1365678/selecting-a-range-of-rows-using-3-different-indices On Mon, Dec 13, 2010 at 5:04 PM, Puneet Kishor wrote: > > > steve mtangoo wrote: > > I have a script that is supposed to query the Bible scriptures between > two > > intervals. My table is named Bible and have columns: ID (int), Book > (int), > > Chapter(int), Verse (int) and Scripture(text). > > > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > > verses. Now suppose I want to get scripture between book 1 chapter 1 > verse 1 > > and book 2 chapter 3 Verse 1, how do I go about? > > > SELECT Scripture > FROM Bible > WHERE > Book > 0 AND Book < 3 AND > Chapter > 0 AND Chapter < 4 AND > Verse > 0 AND Verse < 2 > > > My knowledge of SQLite3 > > have taken me to a dead end! > > You need to acquire knowledge of SQL, not sqlite3. Google for tutorials. > > > > > You can check how bible have its chapters and verses arranged at > > www.biblegateway.com. > > > > Thanks! > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Puneet Kishor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 13 Dec 2010, at 1:54pm, steve mtangoo wrote: > I have a script that is supposed to query the Bible scriptures between two > intervals. My table is named Bible and have columns: ID (int), Book (int), > Chapter(int), Verse (int) and Scripture(text). > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 > and book 2 chapter 3 Verse 1, how do I go about? Unfortunately, with the structure you describe this is difficult because it is really three searches: Book 1 from c1v1 onwards ... ... then all the books between book 1 and book 2 (there aren't any) ... ... then all of book 2 up to c2v1. Worse still, the first and last of those have to cope with chapters being broke up into verses. This makes even those parts complicated. I will show one possible solution to make the searching possible with a simple search command. It is to make up a hash string to use as an index. You only need two digits for the Books but let's use three for them all because you need three for chapters and versus (because Psalms has 150 chapters and psalm 119 has 176 verses. So make up a string like this: BbbbCcccVvvv bbb = book number ccc = chapter number vvv = verse number So Book 5 Chapter 6 Verse 7 would be 'B005C006V007'. You can store this string in a new column in your table. Call it 'hash'. And make an index for it. You will have to set the values up somehow: you could write some code in your programming language to do it. To left pad a number with zeros to make it three digits take the number add 1000 to it take the right-most three characters of the result. Now to look up all the verses between a start and end verse you can now just search the hash strings. So for your example search you would do something like SELECT * FROM Bible WHERE hash BETWEEN 'B001C001V001' AND 'B002C003V001' ORDER BY hash and it will find all the right verses in the right order. > My knowledge of SQLite3 > have taken me to a dead end! Try some basic tutorials on SQL. You should find plenty using Google. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > I have a script that is supposed to query the Bible scriptures between two > intervals. My table is named Bible and have columns: ID (int), Book (int), > Chapter(int), Verse (int) and Scripture(text). > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 > and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQLite3 > have taken me to a dead end! Here's a straighforward answer: select * from Bible where (book > :bookLow or (book=:bookLow and (chapter>:chapterLow or (chapter=:chapterLow and verse>=:verseLow) and (book < :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or (chapter=:chapterHigh and verse<=:verseHigh); Here's a trick to make it shorter: select * from Bible where book*100 + chapter*1000 + verse between :bookLow*100 + :chapterLow*1000 + :verseLow and :bookHigh*100 + :chapterHigh*1000 + :verseHigh; Basically, the idea is to assign each verse a unique number (the query above assumes there are no more than 1000 verses per chapter and chapters per book; adjust multipliers if there are more). You may want to consider working with such sequential numbers internally in your program, converting between them and (book, chapter, verse) format on input/output only. So, define your table as Bible(VerseNumber integer primary key, Scripture text), and the query becomes simply select * from Bible where VerseNumber between :VerseLow and :VerseHigh; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
Puneet Kishor wrote: > steve mtangoo wrote: >> I have a script that is supposed to query the Bible scriptures between two >> intervals. My table is named Bible and have columns: ID (int), Book (int), >> Chapter(int), Verse (int) and Scripture(text). >> >> Now the books are unique i.e. 1-66 but chapters keep repeating, and so do >> verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 >> and book 2 chapter 3 Verse 1, how do I go about? > > > SELECT Scripture > FROM Bible > WHERE > Book > 0 AND Book < 3 AND > Chapter > 0 AND Chapter < 4 AND > Verse > 0 AND Verse < 2 This excludes, for example, book 1 chapter 10. I'm pretty sure the OP wanted it included. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
steve mtangoo wrote: > I have a script that is supposed to query the Bible scriptures between two > intervals. My table is named Bible and have columns: ID (int), Book (int), > Chapter(int), Verse (int) and Scripture(text). > > Now the books are unique i.e. 1-66 but chapters keep repeating, and so do > verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 > and book 2 chapter 3 Verse 1, how do I go about? SELECT Scripture FROM Bible WHERE Book > 0 AND Book < 3 AND Chapter > 0 AND Chapter < 4 AND Verse > 0 AND Verse < 2 > My knowledge of SQLite3 > have taken me to a dead end! You need to acquire knowledge of SQL, not sqlite3. Google for tutorials. > > You can check how bible have its chapters and verses arranged at > www.biblegateway.com. > > Thanks! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to write Query to do this?
I have a script that is supposed to query the Bible scriptures between two intervals. My table is named Bible and have columns: ID (int), Book (int), Chapter(int), Verse (int) and Scripture(text). Now the books are unique i.e. 1-66 but chapters keep repeating, and so do verses. Now suppose I want to get scripture between book 1 chapter 1 verse 1 and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQLite3 have taken me to a dead end! You can check how bible have its chapters and verses arranged at www.biblegateway.com. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
